BitmapCake!

You can list the users with the following command:
\du
Edit the /etc/postgresql/9.6/main/postgresql.conf file and do the following changes.

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/9.6/main/archive/%f && cp %p /var/lib/postgresql/9.6/main/archive/%f''
Now we need to edit the pg_hba.conf file and add a record for the standy server so that it can connect to the primary.

host replication replication 192.168.3.169/32 md5

Note: 192.168.3.169 is the IP address of standby server.

Now create a new directory inside of the 'main' directory for the archive configuration - run the below command as postgres user:

mkdir -p /var/lib/postgresql/9.6/main/archive/

On your Standby Server
===================

service postgresql stop
We shall keep a backup of the postgres data directory.

mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main_backup
Now we should migrate the data dir from the master server using the pg_basebackup tool.

pg_basebackup -h 192.168.4.168 -D /var/lib/postgresql/9.6/main -U replication -v -P

Note: 192.168.4.168 is the IP address of the Master server.

After the base backup is complete, we should create a recovery.conf file.

/var/lib/postgresql/9.6/main/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.4.168 port=5432 user=replication password=123'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger'
Switch back to the root user.

exit
Start the Postgres service

service postgresql start
Verification
==========

Now we have finished setting up streaming replication between the two servers. To verify that both servers are working as expected, switch to the postgres user and run the following command in the Master Server.

su - postgres
psql -x -c "select * from pg_stat_replication;"
You should see the client_address property pointed to standy server's IP address and the state as streaming.

Note: I took the above restore and archive commands from the Postgresql documentation here: https://www.postgresql.org/docs/9.6/continuous-archiving.html

That's it. I tried my best to keep it as simple as possible. This is only a basic setup to give you an idea on how to setup streaming replication. For a production environment, you might need to set proper values depending on the requirements.

I hope this post will be helpful for someone :) Don't forget to leave a comment if it helped.

I was setting up streaming replication between two Postgresql servers in active passive mode. After the setup was done, I tried to connect to the slave node to see if everything works fine and received the following error:

FATAL:  the database system is starting up

I was thinking for a while before I realized that I didn't set the slave node to hot_standby mode. It seems like a common mistake everyone does, so I thought of writing it down here hoping someone would find it useful.

If you get this error, this is what you have to do:

Open your postgresql.conf file.

vim /etc/postgresql/9.6/main/postgresql.conf
Locate the following

hot_standby = off
and change it as follows:

hot_standby = on

Save the file, then:

service postgresql restart

Now you can query your slave server ;)

Today I was installing Postgres 9.6 in Ubuntu 14 server and while adding the key, I got this error.

gpg: no valid OpenPGP data found error

This is the command I entered:

wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

I tried several solutions and found a way to get past this error. To solve, all you have to do is download the key separately and add it.

Here we basically the run the above in two seperate commands. This is how to do it.

First get the key.

wget https://www.postgresql.org/media/keys/ACCC4CF8.asc

Then add it:

sudo apt-key add ACCC4CF8.asc

That's it. Hope it will help someone ;)

I have been using Tmux for some time with Ubuntu 16.04 and it was all working so well until I upgraded my system to Ubuntu 18.04. When I tried to run tmux via the command, it interrupted with an error as shown below:

tmux: error while loading shared libraries: libevent-2.0.so

So I checked a little bit deeper and found the solution to this. It seems like tmux in Ubuntu 18.04 requires the shown version of libevent and since I installed tmux under Ubuntu 16.04, I'm missing it. That's pretty obvious.

You should also note that Tmux comes with all the required libraries in Ubuntu repositories if you use Ubuntu 18.04. So in order to fix it, all you have to do is remove the version of tmux you already have and reinstall it.

First let's remove tmux.

sudo apt purge tmux*
This will remove all tmux related packages from your system. Now if you enter the command tmux in your terminal, you should get a message saying it's not installed. If it still shows the same error, then you have to manually remove tmux.

To see which tmux binary is used in your system, run the following command.

which tmux

It will display the path to tmux. You can remove it manually by running:

sudo rm $(which tmux)

Fine, now tmux is removed from your system. Now let's install it using apt.

sudo apt install tmux

After installing, you can start tmux by entering the tmux command in your terminal. If you get some error saying tmux is not found, then its probably because your system is still using the old path to tmux, which we deleted before.

To verify that, enter the following command.

type tmux

If you get a response something to similar to this :

tmux is hashed (/usr/local/bin/tmux)

Then it means you have to clear the old hash, so it will identify the new tmux path.

You can remove the hash by running the following command.

hash -d tmux

This will remove the hash entry and now if you again try to run tmux, it should work as expected.

I hope this will help somebody :) Feel free to comment and ask any questions.