Useful Postgresql commands/sqls for beginners

The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running
SELECT pg_is_in_recovery();
The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;
The current transaction has been started with. You can find out if that is the case using the undocumented parameter
SHOW transaction_read_only;
where is pgdata?
SHOW data_directory;
Replication user is active?
select * from pg_stat_activity where usename = 'replicator';

PSQL Listing Commands


\x on; --extended display
\c db; --connect to database
\du+; --list users
\dt+ schema.tablename; --lists tables
\df+ schema.func/procname;
\d+  schema.tablename; -- gives description of table

Minimal Postgresql 11 Replication on Ubuntu 18.04

I have two Virtual Machines/Servers with Ubuntu 18.04 installed on it. On both these machines I have Postgresql 11 installed. Let us say the IP of master is 127.10.10.1 and IP of slave is 127.10.10.2

Changes to Master

Firstly we shutdown master

sudo service postgresql stop

Go to psql as postgres and create a replication user called replicator.

CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'replica'; 

Go to pg_hba.conf file which is in /etc/postgresql/11/main folder

add replication user with slave ip

#add replication user with slave ip
host replication replicator 127.10.10.2/32 md5

Go to postgresql.conf file which is in /etc/postgresql/11/main folder

#change listen_address to allow all ips with  
# listen_addresses = '*'
# OR mention specifically the slave ip
listen_addresses = 'localhost, 127.10.10.2'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Start pg master

sudo service postgresql start

Slave Configuration

Stop the slave

sudo service postgresql stop

Go to pg_hba.conf file which is in /etc/postgresql/11/main folder

add replication user with master ip

#add replication user with master ip
host replication replicator 127.10.10.1/32 md5

Go to postgresql.conf file which is in /etc/postgresql/11/main folder

#change listen_address to allow all ips with  
# listen_addresses = '*'
# OR mention specifically the slave ip
listen_addresses = 'localhost, 127.10.10.2'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

One more very important part of the postgresql.conf to do is set the slave in standby mode. That way it is in “read only” and just replicates events that happen in the master.

standby_mode = 'on'

Now we need to do a “first” copy of data from the master to the slave. Which implies we need to delete all data in postgresql on the slave and take everything thats on the master. Now first let’s find out where the postgresql data (PGDATA) is .

-- login as user postgres
$ sudo -i -u postgres
--login to psql
$psql
--to find out where pgdata is:
$show data_directory;
data_directory
/var/lib/postgresql/11/main
(1 row)

Now we go to that folder and make a backup and remove main. Please note that one has to be logged in as user postgres in order to access main folder

$mv main main_bkup
$rm -rf main

Here comes the real deal now. We have to connect to the master and take the master’s pgdata and move it to slave. There’s one elegant command for that:

$ pg_basebackup -h master-ip -D /var/lib/postgresql/11/main/ -P -U  replicator --wal-method=stream
-- when this run, the following output is printed
Password:
waiting for checkpoint
0/46100 kB (0%), 0/1 tablespace
17829/46100 kB (38%), 0/1 tablespace
46109/46109 kB (100%), 0/1 tablespace
46109/46109 kB (100%), 1/1 tablespace

Now we go inside the main folder and create a recovery.conf file. This too has to be done as postgres user.

$ cd /var/lib/postgresql/11/main/
$ touch recovery.conf

The contents of recovery.conf are

standby_mode         = 'on'
primary_conninfo = 'host=master-ip port=5432 user=replicator password=my_replicator_password'
trigger_file = '/tmp/MasterNow'

Start pg slave

sudo service postgresql start

Testing Replication

On the master, see if the replication user shows activity

select * from pg_stat_activity where usename = 'replicator';
--gives the following output
-[ RECORD 1 ]----+------------------------------
datid |
datname |
pid | 27253
usesysid | 16597
usename | replicator
application_name | walreceiver
client_addr | 127.10.10.2
client_hostname |
client_port | 42290
backend_start | 2019-07-03 09:15:13.939534+02
xact_start |
query_start |
state_change | 2019-07-03 09:15:13.948509+02
wait_event_type | Activity
wait_event | WalSenderMain
state | active
backend_xid |
backend_xmin |
query |
backend_type | walsender

You can now create a table in master add rows to it

--in psql from master--
create table test (msg varchar(255), created_on date);
insert into test values ('test1' , current_date);

We can now select these values from the slave.

--in psql from slave --
select * from test;
-[ RECORD 1 ]-----
msg | test1
datum | 2019-07-03

When we try to insert in the slave then we get error

insert into test values ('hari', current_date);
ERROR: cannot execute INSERT in a read-only transaction

This is because the slave is in standby mode. It can only be replicated from master. You can check these values with following commands:

The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running
SELECT pg_is_in_recovery();
--The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;

Happy replicating!! At this point i would also like to thank the innumerable blogs of other great techies that put out this information out there.