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 "",


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

$ 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  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'


$ 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

Saturday, March 6, 2021

Configuring Mysql Asynchronous Replication

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



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='',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.