Skip to content

Instantly share code, notes, and snippets.

@uuhnaut69

uuhnaut69/Postgres-Hand-On-Notes.md

Last active November 1, 2020 10:24
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save uuhnaut69/d65373654900f7d767cab3cbc7e3acb3 to your computer and use it in GitHub Desktop.
Save uuhnaut69/d65373654900f7d767cab3cbc7e3acb3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Postgres-Hand-On-Notes.md

Get Started for Mac OS

Install Vagrant

Download & install vagrant here.

Start Vagrant

mkdir example && cd example 
vagrant init centos/7 
vagrant up 
vagrant ssh

Install Postgres & Pgbackrest

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  • Install required libraries
sudo yum -y install epel-release
sudo yum -y install libzstd-devel
  • Install Postgres & Pgbackrest
sudo yum -y install postgresql12-server postgresql12-contrib pgbackrest
  • Initialize the PostgreSQL instance
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb 
sudo systemctl enable postgresql-12.service 
sudo systemctl start postgresql-12.service
  • Verify PostgreSQL
sudo -iu postgres 
psql --version psql (PostgreSQL) 12.1
$ pgbackrest 
pgBackRest 2.19 - General help 
Usage: 
    pgbackrest [options] [command] 
    
Commands: 
     archive-get     Get a WAL segment from the archive. 
     archive-push    Push a WAL segment to the archive. 
     backup          Backup a database cluster. 
     check           Check the configuration. 
     expire          Expire backups that exceed retention. 
     help            Get help. 
     info            Retrieve information about backups. 
     restore         Restore a database cluster. 
     stanza-create   Create the required stanza data. 
     stanza-delete   Delete a stanza. 
     stanza-upgrade  Upgrade a stanza. 
     start           Allow pgBackRest processes to run. 
     stop            Stop pgBackRest processes from running. 
     version         Get version. 
     
 Use 'pgbackrest help [command]' for more information.
  • Find postgres config file directory
sudo -iu postgres psql -U postgres -c 'SHOW config_file' 
config_file 
---------------------------------------- 
/var/lib/pgsql/12/data/postgresql.conf 
(1 row)
  • Install vim editor
sudo yum install -y vim
  • Update postgresql.conf
listen_addresses = '*' 
password_encryption='scram-sha-256'
archive_mode = on
  • Restart postgresql
sudo systemctl restart postgresql-12.service
  • Check configuration via psql
sudo -iu postgres psql
SELECT name,setting,context,source FROM pg_settings WHERE NAME IN ('listen_addresses','archive_mode','password_encryption'); 
      name           |  setting          |    context   |    source 
---------------------+---------------+------------+-------------------- 
archive_mode         |   on              |   postmaster | configuration file 
listen_addresses     |   *               | postmaster   | configuration file 
password_encryption  | scram-sha-256     | user         | configuration file 
(3 rows)
  • Exit postgres
\q

Configure pgBackRest

sudo mkdir -p /var/lib/pgbackrest 
sudo chmod 0750 /var/lib/pgbackrest 
sudo chown -R postgres:postgres /var/lib/pgbackrest
  • Configure the location and permissions on the pgbackrest log location:
sudo chown -R postgres:postgres /var/log/pgbackrest
  • Create backup of pgbackrest file config
sudo cp /etc/pgbackrest.conf /etc/pgbackrest.conf.backup
  • Generate password
openssl rand -base64 48
  • Edit pgbackrest.conf
[global]
repo1-cipher-pass=uUQsaa7+CCFaqXVagFzNUix3XuLe9e2uqVskqfI6wcKf8BX8y5b+8bL3oimRpV1N
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
log-level-console=info
log-level-file=debug

[demo]
pg1-path=/var/lib/pgsql/12/data
  • Initialize Pgbackrest stanza (Currently i've initialized, console maybe different)
sudo -u postgres pgbackrest --stanza=demo stanza-create 

2020-10-31 06:54:28.875 P00   INFO: stanza-create command begin 2.30: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/12/data --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
2020-10-31 06:54:29.502 P00   INFO: stanza 'demo' already exists and is valid
2020-10-31 06:54:29.502 P00   INFO: stanza-create command end: completed successfully (629ms)

Pulling It All Together, Performing First Backup

  • Edit postgresql.conf => This configuration option informs PostgreSQL to use pgBackRest to handle the WAL segments, pushing them immediately to the archive.
archive_command = 'pgbackrest --stanza=demo archive-push %p'
  • Reload postgres service
sudo systemctl reload postgresql-12.service
  • Check
sudo -iu postgres pgbackrest --stanza=demo check 

2020-10-31 06:57:55.675 P00   INFO: check command begin 2.30: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/12/data --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
2020-10-31 06:57:56.614 P00   INFO: WAL segment 000000020000000000000008 successfully archived to '/var/lib/pgbackrest/archive/demo/12-1/0000000200000000/000000020000000000000008-ae836af9167b4c345e0800b128ea3d6a0f60e415.gz'
2020-10-31 06:57:56.614 P00   INFO: check command end: completed successfully (939ms)
  • Perform full backup
sudo -u postgres pgbackrest --stanza=demo --type=full backup 

...
2020-10-31 06:59:16.402 P00   INFO: new backup label = 20201031-065911F
2020-10-31 06:59:16.452 P00   INFO: backup command end: completed successfully (6049ms)
2020-10-31 06:59:16.452 P00   INFO: expire command begin 2.30: --log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
2020-10-31 06:59:16.454 P00   INFO: expire full backup 20201031-053726F
2020-10-31 06:59:16.459 P00   INFO: remove expired backup 20201031-053726F

Restore a Backup

  • Stop the PostgreSQL instance, and delete its data files, simulating a system administration disaster.
sudo systemctl stop postgresql-12.service 
sudo find /var/lib/pgsql/12/data -mindepth 1 -delete
  • When we restart postgres will be throw error
sudo systemctl start postgresql-12.service 
## THIS WILL FAIL 

Job for postgresql-12.service failed because the control process exited with error code. See "systemctl status postgresql-12.service" and "journalctl -xe" for details.
  • Perform restore
sudo -iu postgres pgbackrest --stanza=demo --delta restore
sudo systemctl start postgresql-12.service
  • Verify pgbackrest is working
sudo -u postgres pgbackrest --stanza=demo check 

2020-10-31 07:02:21.766 P00   INFO: WAL segment 00000002000000000000000B successfully archived to '/var/lib/pgbackrest/archive/demo/12-1/0000000200000000/00000002000000000000000B-cd5d98b1a37a8a96c0af9aca45ee2d4adeee9989.gz'
2020-10-31 07:02:21.766 P00   INFO: check command end: completed successfully (936ms)

Init SQL

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

create table companies
(
    id           uuid primary key default uuid_generate_v4(),
    company_name varchar not null,
    address      varchar not null
);

insert into companies(company_name, address)
values ('Company A', 'Viet Nam'),
       ('Company B', 'Viet Nam'),
       ('Company C', 'Viet Nam');
sudo -u postgres pgbackrest --stanza=demo --type=full backup
  • Insert some data
sudo -iu postgres psql
insert into companies(company_name,address) values ('Another Company A', 'California, US'), ('Another Company B', 'California, US');
  • Make differential backup instead of full backup => backup faster (Get diff from last full backup)
sudo -u postgres pgbackrest --stanza=demo --type=diff backup
  • Make incremential backup instead of full backup => backup faster (Get diff from last full backup)
sudo -u postgres pgbackrest --stanza=demo --type=diff backup

Postgres Replicate Docker Setup

version: "3.3"
services:
  primary:
    hostname: "primary"
    image: crunchydata/crunchy-postgres:centos7-11.9-4.5.0
    environment:
      - PGHOST=/tmp
      - MAX_CONNECTIONS=10
      - MAX_WAL_SENDERS=5
      - PG_MODE=primary
      - PG_PRIMARY_USER=primaryuser
      - PG_PRIMARY_PASSWORD=password
      - PG_DATABASE=testdb
      - PG_USER=testuser
      - PG_PASSWORD=password
      - PG_ROOT_PASSWORD=password
      - PG_PRIMARY_PORT=5432
    volumes:
      - pg-primary-vol:/pgdata
    ports:
      - "5432"
    networks:
      - crunchynet
    deploy:
      placement:
        constraints:
          - node.labels.type == primary
          - node.role == worker
  replica:
    image: crunchydata/crunchy-postgres:centos7-11.9-4.5.0
    environment:
      - PGHOST=/tmp
      - MAX_CONNECTIONS=10
      - MAX_WAL_SENDERS=5
      - PG_MODE=replica
      - PG_PRIMARY_HOST=primary
      - PG_PRIMARY_PORT=5432
      - PG_PRIMARY_USER=primaryuser
      - PG_PRIMARY_PASSWORD=password
      - PG_DATABASE=testdb
      - PG_USER=testuser
      - PG_PASSWORD=password
      - PG_ROOT_PASSWORD=password
    volumes:
      - pg-replica-vol:/pgdata
    ports:
      - "5432"
    networks:
      - crunchynet
    deploy:
      placement:
        constraints:
          - node.labels.type != primary
          - node.role == worker
networks:
  crunchynet:

volumes:
  pg-primary-vol:
  pg-replica-vol:
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment