Postgresql 13 Streaming Replication using Docker

In this setup, we will create a 2 nodes postgresql streaming replication, in docker. This article will make use of postgresql image version 13.

1. Create a network, and take note of the network ip range

$ docker network create mynet

$ docker network inspect mynet 

2. Make 1 directory for pgmaster. 

$ sudo mkdir pgmasterdata

3.  Create a container called pgmaster

$ docker run -dit -v "$PWD/pgmasterdata/:/var/lib/postgresql/data -e POSTGRES_PASSWORD=abc -p 5432:5432 --restart=unless-stopped --network=mynet --name=pgmaster postgres 

4. Backup and edit pgmaster's postgresql.conf with below settings

$ sudo cp pgmasterdata/postgresql.conf pgmasterdata/postgresql.conf.ori

$ cat > postgresql.conf <<EOF

listen_addresses = '*'

port = 5432

max_connections = 50

ssl = off

shared_buffers = 32MB

# Replication Settings - Master

wal_level = hot_standby

max_wal_senders = 3

EOF

$ sudo cp postgresql.conf pgmasterdata/postgresql.conf 

5. Login to pgmaster and create a user for replication

$ docker exec -it pgmaster psql -U postgres -h localhost -d postgres

postgres=# create role replicator with replication password 'abc';

postgres=# \q

 

6. Backup and edit pgmaster's pg_hba.conf with ip range from step 1

$ sudo cp pgmasterdata/pg_hba.conf pgmasterdata/pg_hba.conf.ori

$ echo "host    replication  all  172.16.0.0/16  trust" | sudo tee -a pgmasterdata/pg_hba.conf 

7. Restart pgmaster container

$ docker restart pgmaster

8.  Run backup of master to /slavedata in pgmaster

$ docker exec -it pgmaster bash

# mkdir /pgslavedata 

# pg_basebackup -h pgmaster -D /pgslavedata -U replicator -v -P --wal-method=stream

9. Copy /slavedata in pgmaster to host

$ docker cp pgmaster:/pgslavedata pgslavedata

10. Tell pgslave that it is a slave

$ sudo touch  pgslavedata/standby.signal

11. Edit postgresql.conf in pgslavedata

$ sudo cp pgslavedata/postgresql.conf pgslavedata/postgresql.conf.ori

$ cat > postgresql.conf <<EOF

listen_addresses = '*'

port = 5432

max_connections = 50

ssl = off

shared_buffers = 32MB

# Replication Settings - Slave

hot_standby = on

primary_conninfo = 'host=<master ip> port=5432 user=replicator password=abc@123'

EOF

$ sudo cp postgresql.conf pgslavedata/postgresql.conf

12. Start pgslave

$ docker run -dit -v "$PWD"/pgslavedata/:/var/lib/postgresql/data -e POSTGRES_PASSWORD=abc -p 15432:5432 --network=mynet --restart=unless-stopped--name=pgslave postgres

13. Check replication state in pgmaster

$ docker exec -it pgmaster psql -h localhost -U postgres -d postgres -c "select usename,state from pg_stat_activity where usename = 'replicator';"

usename   | state  

------------+--------

 replicator | active 

14. Verify the setup by creating a database in pgmaster, and check if the same database appear in pgslave. 

15. To promote to pgslave if pgmaster is down, simply run "pg_ctl promote" command

$ docker exec -it pgslave bash

# pg_ctl promote

Configuring Mysql Asynchronous Replication

In this exercise, we will use one master and one slave. The addresses are as follow:

master: 10.0.0.10

slave: 10.0.0.20

1. Make sure mysql-server is installed in both machines

2. In master, check the user_id

$ mysql -u root -p

mysql> show variables like 'server_id';

3. In master, create a user for replication

mysql> create user replicator@'%' identified with 'mysql_native_password' by 'mypassword';

3. In master, grant the user a privilege of slave replication

mysql> grant replication slave on *.* to replicator@'%';

4. In slave, change the server_id to a number different from the master

$ mysql -u root -p

mysql> set global variable server_id = 20;

5. Configure slave with the server's details

mysql> change master to master_host='10.0.0.10',master_user='replicator',master_password='mypassword';

6.  Start slave

mysql> start replica;

7. Check slave status. Make sure slave_io and slave_sql are in running state.

mysql> show slave status\G

8. Test the setup. Create database, add table and some data into master, and check if the data gets replicated to slave. 

Connecting to GlobalProtect VPN in Linux Mint

I encountered GlobalProtect (GP) vpn while working on a project, and somehow the vpn portal does not have any linux client for me to connect to the server. They have windows and mac though, so I tried searching around for solution.

After a while, I stumbled upon this post and this other post, stating that openconnect client can connect to GP vpn.

To install openconnect is fairly easy. Just fire up your terminal, and use below command to install openconnect client

$ sudo apt install openconnect -y

Once installed, you just have to use below command to connect to your GP vpn

$ sudo openconnect --protocol gp -u foo vpn.server.com

You will get some warning about "Certificate failed verification", just answer yes

Certificate from VPN server "vpn.server.com" failed verification.

Reason: signer not found

To trust this server in future, perhaps add this to your command line:

    --servercert pin-sha256:1YWmjjGL3wppl245dRc3/p+mytteBnvaVz456DQY+wutt=

Enter 'yes' to accept, 'no' to abort; anything else to view: yes 

It will later ask for you password, just put in your password

Connected to HTTPS on vpn.server.com

Enter login credentials

Password: 

You will know that you are connected if you find something resembles below line

Connected as 192.168.100.72, using SSL, with ESP in progress

Try to access your internal server, and you should be able to.

Rejoice!

Displaying Text on Remote Desktop over SSH

I used this trick to warn my kids that their computer playing time is almost over. The application name is zenity, and we need to set our DISPLAY environment to :0 beforehand so that the message will appear on their screen.

1. ssh into the machine

$ ssh foo@machine

2. Set the DISPLAY environment

$ export DISPLAY=:0

3. Use zenity to prompt messages to their screen. In this case, I use warning style message, and will be displayed for 2 seconds

$ zenity --warning --timeout=2 --text="Computer will be shut down in 15 minutes"

Replacing Newline with Space

Let's say we have a list of words like below

$ cat animals

cat

fish

zebra

monkey

and we want it to be arranged in one horizontal line, separated by space. We can achieve that by using tr command.

$ cat animals | tr "\n" " " 

cat fish zebra monkey

What happened is, \n (symbol for newline), will be replaced with " ", which is symbol for space.