# PostgreSQL Cheat Sheet

- [Common queries](#common-queries)
- [Init DB](#init-db)
- [Replication](#replication)



### Common queries

  ```bash

  ```

### Init DB

  ```bash

  su - postgres
  rm -rf /var/lib/postgresql/10/main/*
  /usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main/
  /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main/ -l logfile start

  ```

### Replication

The primary server operates in continuous archiving mode,<br>
while each standby server operates in continuous recovery mode, reading the WAL files from the primary.

- On both servers:

  ```bash
  # In postgresql.conf:
  wal_level = replica
  hot_standby = on
  ```

- On the master:

  ```bash
  # create repl user
  CREATE ROLE repl LOGIN REPLICATION ENCRYPTED PASSWORD 'verysecret';

  # create a replication slot
  SELECT * FROM pg_create_physical_replication_slot('standby_replication_slot');

  # Add to pg_hba.conf:
  host    replication     repl            SLAVEIP/32        md5
  ```

- On the slave:

  ```bash
  # In /var/lib/postgresql/10/main/recovery.conf
  primary_conninfo = 'host=MASTERIP port=5432 user=repl password=verysecret'
  primary_slot_name = 'standby_replication_slot'
  standby_mode = on
  trigger_file = '/etc/postgresql/10/main/failover.trigger'

  # Init replica
  systemctl stop postgresql
  rm -rf /var/lib/postgresql/10/main/*
  su - postgres -c "pg_basebackup --wal-method=stream -D /var/lib/postgresql/10/main/ -U repl -h MASTERIP"
  ```

- Check replication status

  - On the master:

    Check the status of the replication:

    ```bash
    select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication;
    ```

  - On the slave:

    pg_is_in_recovery will return "t" in the replica, and "f" in the master.

    ```bash
    select pg_is_in_recovery();
    ```

    The following query will return 1 in the slave, 0 in the master:

    ```bash
    SELECT count(*) from pg_settings where name = 'hot_standby' AND setting = 'on';
    ```