Wednesday, March 10, 2021

Fixing Wrong Timezone in Docker Logs

I had this issue of my running container logs is using UTC as its timezone, which will make troubleshooting process quite troublesome.

After some experiment, I found that we have to set the timezone early when we run the container, to make the logs recorded in the timezone we prefer.

For currently running container, the safest way is to commit it to image, and rerun a new container based on that image.

1. My current container's logs

$ docker logs mycontainer

...

2021-03-09 12:01:08.939 UTC [1283819] WARNING:  terminating connection because of crash of another server process

...

2. Back up the current container to an image called mycontainer-backup:20210310

$ docker commit mycontainer mycontainer-backup:20210310

3. Stop the current container, to avoid port clash (if any)

$ docker stop mycontainer

4. Run a new container, based of the backup image, this time we need to specify the timezone variable

$ docker run -dit --name mynewcontainer -e TZ=Asia/Kuala_Lumpur mycontainer-backup:20210310

5. Done, we can verify the log's timezone, just to be sure

$ docker logs mynewcontainer

...

2021-03-10 08:03:14.429 +08 [1] LOG:  listening on IPv4 address "0.0.0.0",

...

Monday, March 8, 2021

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 3 directories for pgmaster, pgdata and pgbootstrap. The bootstrap directory will be used to setup pgslave the first time.

$ sudo mkdir -p /dockervol/{pgmaster,pgslave,pgbootstrap}
3.  Create a container called pgmaster
$ docker run -dit -v /dockervol/pgmaster/:/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 /dockervol/pgmaster/postgresql.conf 
/dockervol/pgmaster/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 /dockervol/pgmaster/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'
6. Backup and edit pgmaster's pg_hba.conf with ip range from step 1
$ sudo cp /dockervol/pgmaster/pg_hba.conf 
/dockervol/pgmaster/pg_hba.conf.ori
$ echo "host    replication  all  172.16.0.0/16  trust" | sudo tee -a /dockervol/pgmaster/pg_hba.conf 
7. Restart pgmaster container
$ docker restart pgmaster
8. Run bootstrap container
$ docker run -dit --rm -v /dockervol/pgbootstrap:/var/lib/postgresql/data -v /dockervol/pgslave:/var/lib/postgresql/slave-data -e POSTGRES_PASSWORD=abc --network=mynet --name=pgbootstrap postgres

9.  Run backup of master to /var/lib/postgresql/slave-data in pgbootstrap

$ docker exec -it pgbootstrap su - postgres

$ pg_basebackup -h pgmaster -D /var/lib/postgresql/slave-data -U replicator -v -P --wal-method=stream

10. Stop pgbootstrap

$ docker stop pgbootstrap

11. Tell pgslave that it is a slave

$ sudo touch /dockervol/pgslave/standby.signal

12. Edit postgresql.conf in /dockervol/pgslave

$ sudo cp /dockervol/pgdata/postgresql.conf /dockervol/pgdata/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 /dockervol/pgdata/postgresql.conf

13. Start pgslave

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

14. 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 

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

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

$ docker exec -it pgslave bash

# su - postgres

# pg_ctl promote

Saturday, March 6, 2021

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.