PostgreSQL Master Configuration

Starting a server as a Master

1. CentOS 6: sudo service postgresql-94 initdb

   CentOS 7: sudo /usr/pgsql-9.4/bin/postgresql-94-setup initdb

2. CentOS 6: sudo service postgresql-94 start

   CentOS 7: sudo systemctl start postgresql-94

The server is now running but before we can issue some SQL queries we need to change the pg_hba.conf file, which has the rules about how to accept connections. You can check here for the documentation about the file but for now you just have to change a single line. It's located on /var/lib/pgsql/9.4/data/pg_hba.conf.

1. Find the line host all all 127.0.0.1/32 ident and replace ident with trust

Note: I use trust here to make it simpler to setup a test system, you should use a more secure option if security is a concern (i.e. production server)

2. On a terminal run sudo service reload postgresql-94 to reload the config file

3. Run pgsql -U postgres -h localhost -p 5432 (check here for more information about the PostgreSQL client)

You can now issue SQL queries. Don't forget to end them with a ;. Exit with \q.

Configuring the Master for Streaming Replication

Open /var/lib/pgsql/9.4/data/postgresql.conf with a text editor then find and change the following lines:

• wal_level = hot_standby
• max_wal_senders = 4 (or however many slaves you plan to use)
• synchronous_standby_names = '*'
• wal_keep_segments = 100
• hot_standby = on

To allow replication from other machines open /var/lib/pgsql/9.4/data/pg_hba.conf add however many lines like these you need (one for each slave) :

host replication replicador 192.168.1.1/32 trust

Note: I use trust here to make it simpler to setup a test system, you should use a more secure option if security is a concern (i.e. production server)

Of course you'll need to change the IP address of each machine. You can also use the subnet mask to add a whole bunch of IP's with a single line, for example host replication replicador 192.168.1.1/24 trust includes all IP's that start with 192.168.1

Then open a terminal and run the following commands:

1. sudo su postgres

2. psql

3. create user replicador replication; (don't forget the semicolon)

4. \du+ (check if the user was indeed created)

5. \q to quit psql

6. exit to go back to your user

7. sudo service postgresql-9.4 restart

And we're done with the Master server for now :)