Setting up Postgres 9.0 Streaming Replication

1 Nov

Goal

  • The instructions in this page are specific to the CentOS VM being used for development purposes. For development all the masters/slave are running in the same server/VM. In production, the masters/slaves will
    be in different servers. Make sure you take into different server IP address settings and shared folder settings if you are using this document for a production deployment.
  • 8 total pgsql instances (listening on different ports).
    • pgsql master shard 1 (pg1)
    • 3 pgsql slaves to master shard 1 (pg1_s1, pg1_s2, pg1_s3)
    • pgsql master shard 2 (pg2)
    • 3 pgsql slaves to master shard 2 (pg2_s1, pg2_s2, pg2_s3)

Shutdown the existing Postgres Service

  • service postgresql-9.0 stop

Create the directories that will contain the 6 pgsql instances:

{info:title=Make Note}

  • It’s uncommon to have multiple postgres instances/services running in the same server. However, for development
    environment purposes we will create multiple instances by creating separate DB directories and specifying different
    ports for each postgres service.

{info}

  • There is already a Database currently in /var/lib/pgsql/9.0/data. We will move this to a new folder as follows:
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1
    • cp -r /var/lib/pgsql/9.0/data /var/lib/pgsql/9.0/shards/pg1

Now the remaining 7 pgsql instances:

  • Shard1
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1_s1
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1_s2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1_s3
  • Shard2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2_s1
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2_s2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2_s3

Setup File permissions

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Setup and Configure Master and Slaves for Shard 1(Ports 5432,5433,5434,5435)

Create an archive directory

  • This is a directory that is shared by the Shard1 masters and slaves.
  • In production this would probably be a Network shared directory. For example, NFS.
  • mkdir -p /var/lib/pgsql/9.0/shards/pg1/archive
  • chown postgres:postgres /var/lib/pgsql/9.0/shards/pg1/archive
  • chmod -R go-rwx /var/lib/pgsql/9.0/shards/pg1/archive

Configure Master Server for Shard1

Edit Master Server pg_hba.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg1/data/pg_hba.conf
  • This sets up authentication for remote slave servers.
  • Add a line at the end of this file as follows. Note for production servers, replace samehost with
    the appropriate slave ip addresses.

/var/lib/pgsql/9.0/shards/pg1/pg_hba.conf

host    replication     pgadmin     samehost           md5

Edit Master Server postgresql.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg1/data/postgresql.conf
  • Edit the following entries as follows:

/var/lib/pgsql/9.0/shards/pg1/data/postgresql.conf

listen_addresses = '*'

wal_level = hot_standby  

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/9.0/shards/pg1/archive/%f'  

max_wal_senders = 5  

wal_keep_segments = 32

Setup and Start Postgres Service Start/Stop Scripts for Master Shard1

  • Rename the existing Postgres service name from postgresql-9.0 to pg1
    • cd /etc/init.d
    • mv postgresql-9.0 pg1
  • Remove the existing Postgres service name from CentOS:
    • chkconfig postgresql-9.0 off
    • chkconfig –del postgresql-9.0
  • Edit the pg1 script so it points to the Database directory. Verify the following entries (LINES 61-63):

    /etc/init.d/pg1

    PGPORT=5432
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1.log
  • Setup the Postgres service name in CentOS:
    • chkconfig –add pg1
    • chkconfig pg1 on
  • Start the service for Master Shard1
  • service pg1 start
  • If startup failed or to ensure proper startup check the latest log file in:
    /var/lib/pgsql/9.0/shards/pg1/data/pg_log/

Get a Snapshot of the Master Server Database

  • Start a Psql session:
    • psql –username=pgadmin –port=5432 –dbname=shard_1
  • After login exectute the following statement to prepare the backup
    • SELECT pg_start_backup(‘shard1_backup’);
    • This command will create a snapshot of the master server
  • Open another terminal window and archive the entire Master Server Data folder
    • cd /var/lib/pgsql/9.0/shards/pg1
    • tar -cjf postgres-pg1.tar.bz2 data
  • In the psql session execute the following command to stop backup mode
    • SELECT pg_stop_backup();

      {info:title=Make Note}

      • Executing the pg_stop_backup() is very important otherwise postgres remains in a backup state
        and might not respond to regular queries.

      {info}

Create Slave instances for Master Shard1

Update Slave DB folder using Master Server Snapshot

  • For pg1_s1
    • cd /var/lib/pgsql/9.0/shards/pg1_s1
    • tar -xjf ../pg1/postgres-pg1.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg1_s2
    • cd /var/lib/pgsql/9.0/shards/pg1_s2
    • tar -xjf ../pg1/postgres-pg1.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg1_s3
    • cd /var/lib/pgsql/9.0/shards/pg1_s3
    • tar -xjf ../pg1/postgres-pg1.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*

Setup the Slave Service Start/Stop Scripts

  • cd /etc/init.d
  • cp pg1 pg1_s1
  • cp pg1 pg1_s2
  • cp pg1 pg1_s3
  • Edit pg1_s1 and change the following entries (LINES 61-63):

    /etc/init.d/pg1_s1

    PGPORT=5433
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1_s1/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1_s1.log
  • Edit pg1_s2 and change the following entries (LINES 61-63):

    /etc/init.d/pg1_s2

    PGPORT=5434
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1_s2/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1_s2.log
  • Edit pg1_s3 and change the following entries (LINES 61-63):

    /etc/init.d/pg1_s3

    PGPORT=5435
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1_s3/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1_s3.log
  • Enable the Slaves as a CentOS service:
    • chkconfig –add pg1_s1
    • chkconfig pg1_s1 on
    • chkconfig –add pg1_s2
    • chkconfig pg1_s2 on
    • chkconfig –add pg1_s3
    • chkconfig pg1_s3 on

Configure the Slave instances for Streaming Replication

  • For pg1_s1
    • cd /var/lib/pgsql/9.0/shards/pg1_s1/data
    • Edit postgresql.conf and mark this instance as hot standby

      /var/lib/pgsql/9.0/shards/pg1_s1/postgresql.conf

      hot_standby=on
    • Create a new file called recovery.conf with the entries below.

      /var/lib/pgsql/9.0/shards/pg1_s1/recovery.conf

      standby_mode          = 'on'
      primary_conninfo      = 'host=localhost port=5432 user=pgadmin password=pgadmin'
      # Specifies a trigger file whose presence should cause streaming replication to
      # end (i.e., failover).
      trigger_file = '/var/lib/pgsql/9.0/shards/pg1/data/trigger'
      restore_command = 'cp /var/lib/pgsql/9.0/shards/pg1/archive/%f %p'
  • For pg1_s2
    • cd /var/lib/pgsql/9.0/shards/pg1_s2/data
    • cp ../../pg1_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg1_s1/data/recovery.conf .
  • For pg1_s3
    • cd /var/lib/pgsql/9.0/shards/pg1_s3/data
    • cp ../../pg1_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg1_s1/data/recovery.conf .

Setup Slave File permissions

We repeat the setting up file permission to ensure any new files created have the same privileges.

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Start Slave Instances

  • service pg1_s1 start
  • service pg1_s2 start
  • service pg1_s3 start
  • If startup failed or to ensure proper startup view these files.
    • For pg1_s1 see the latest log file in /var/lib/pgsql/9.0/shards/pg1_s1/data/pg_log/
    • For pg1_s2 see the latest log file in /var/lib/pgsql/9.0/shards/pg1_s2/data/pg_log/
    • For pg1_s3 see the latest log file in /var/lib/pgsql/9.0/shards/pg1_s3/data/pg_log/

Verify Streaming Replication processes

  • To verify streaming replication run the following command:
    ps -ef | grep wal
  • If your setup is valid you should see:
    • a “wal write” process – one per master
    • 3 “wal receiver” processes – one for each slave
    • 3 “wal sender” processes – one for each slave

Test Replication

  • In a terminal window start a psql session and connect to the Master:
    psql –username=pgadmin –port=5432 –dbname=shard_1
  • Execute the following SQL statements:
    CREATE TABLE test (test varchar(30));
    INSERT INTO test VALUES (‘Testing 1 2 3’);
  • In another terminal window start a psql session and connect to slave 1:
    psql –username=pgadmin –dbname=shard_1 –port=5433
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 2:
    psql –username=pgadmin –dbname=shard_1 –port=5434
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 3:
    psql –username=pgadmin –dbname=shard_1 –port=5435
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • Now in the Master psql session execute the statement:
    DROP TABLE test;
  • In any one of the slave psql sessions run:
    SELECT * from test;
  • The previous command will generate an error.

Setup and Configure Master and Slaves for Shard 2(Ports 7432,7433,7434,7435)

Initialize Shard2 Master with Shard1 Master Database files

  1. cd /var/lib/pgsql/9.0/shards/pg2
  2. tar -xjf ../pg1/postgres-pg1.tar.bz2
  3. cd data
  4. rm -f postmaster.pid
  5. rm -f pg_log/*

Create an archive directory for Shard2

  • This is a directory that is shared by the Shard2 masters and slaves.

{info:title=Make Note}

  • In production this would probably be a Network shared directory. For example, NFS.

{info}

  • mkdir -p /var/lib/pgsql/9.0/shards/pg2/archive
  • chown postgres:postgres /var/lib/pgsql/9.0/shards/pg2/archive
  • chmod -R go-rwx /var/lib/pgsql/9.0/shards/pg2/archive

Configure Master Server for Shard2

Edit Shard2 Master Server pg_hba.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg2/data/pg_hba.conf
  • This sets up authentication for remote slave servers.
  • Add a line at the end of this file as follows. Note for production servers, replace samehost with
    the appropriate slave ip addresses.

/var/lib/pgsql/9.0/shards/pg2/pg_hba.conf

host    replication     pgadmin     samehost           md5

Edit Shard2 Master Server postgresql.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg2/data/postgresql.conf
  • Edit the following entries as follows:

/var/lib/pgsql/9.0/shards/pg2/data/postgresql.conf

listen_addresses = '*'

wal_level = hot_standby  

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/9.0/shards/pg2/archive/%f'  

max_wal_senders = 5  

wal_keep_segments = 32

Setup Shard_2 File permissions

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Setup and Start Postgres Service Start/Stop Scripts for Master Shard2

  • Create pg2 startup script using pg1
    • cd /etc/init.d
    • cp pg1 pg2
  • Edit the pg2 script with new PORT and the Database directory. Verify the following entries (LINES 61-63):

    /etc/init.d/pg2

    PGPORT=7432
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2.log
  • Setup the Postgres service name in CentOS:
    • chkconfig –add pg2
    • chkconfig pg2 on
  • Start the service for Master Shard1
  • service pg2 start
  • If startup failed or to ensure proper startup check the latest log file in:
    /var/lib/pgsql/9.0/shards/pg2/data/pg_log/

Dump Shard1 Schema and Create Shard2 Database with schema only

  • cd /var/lib/pgsql/9.0/shards/pg2/data
  • pg_dump –username=pgadmin –port=7432 shard_1 > myapp.schema
  • dropdb –username=pgadmin –port=7432 shard_1
  • createdb –username=pgadmin –port=7432 shard_2
  • psql –username=pgadmin –port=7432 -f myapp.schema shard_2

Get a Snapshot of the Shard2 Master Server Database

  • Start a Psql session:
    • psql –username=pgadmin –port=7432 –dbname=shard_2
  • After login exectute the following statement to prepare the backup
    • SELECT pg_start_backup(‘shard2_backup’);
    • This command will create a snapshot of the master server
  • Open another terminal window and archive the entire Master Server Data folder
    • cd /var/lib/pgsql/9.0/shards/pg2
    • tar -cjf postgres-pg2.tar.bz2 data
  • In the psql session execute the following command to stop backup mode
    • SELECT pg_stop_backup();

      {info:title=Make Note}

      • Executing the pg_stop_backup() is very important otherwise postgres remains in a backup state
        and might not respond to regular queries.

      {info}

Create Shard2 Slave instances for Master Shard2

Update Shard2 Slave DB folder using Master Server Snapshot

  • For pg2_s1
    • cd /var/lib/pgsql/9.0/shards/pg2_s1
    • tar -xjf ../pg2/postgres-pg2.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg2_s2
    • cd /var/lib/pgsql/9.0/shards/pg2_s2
    • tar -xjf ../pg2/postgres-pg2.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg2_s3
    • cd /var/lib/pgsql/9.0/shards/pg2_s3
    • tar -xjf ../pg2/postgres-pg2.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*

Setup the Shard2 Slave Service Start/Stop Scripts

  • cd /etc/init.d
  • cp pg2 pg2_s1
  • cp pg2 pg2_s2
  • cp pg2 pg2_s3
  • Edit pg2_s1 and change the following entries (LINES 61-63):

    /etc/init.d/pg2_s1

    PGPORT=7433
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2_s1/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2_s1.log
  • Edit pg2_s2 and change the following entries (LINES 61-63):

    /etc/init.d/pg2_s2

    PGPORT=7434
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2_s2/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2_s2.log
  • Edit pg2_s3 and change the following entries (LINES 61-63):

    /etc/init.d/pg2_s3

    PGPORT=7435
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2_s3/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2_s3.log
  • Enable the Slaves as a CentOS service:
    • chkconfig –add pg2_s1
    • chkconfig pg2_s1 on
    • chkconfig –add pg2_s2
    • chkconfig pg2_s2 on
    • chkconfig –add pg2_s3
    • chkconfig pg2_s3 on

Configure the Shard2 Slave instances for Streaming Replication

  • For pg2_s1
    • cd /var/lib/pgsql/9.0/shards/pg2_s1/data
    • Edit postgresql.conf and mark this instance as hot standby

      /var/lib/pgsql/9.0/shards/pg2_s1/postgresql.conf

      hot_standby=on
    • Create a new file called recovery.conf with the entries below.

      /var/lib/pgsql/9.0/shards/pg2_s1/recovery.conf

      standby_mode          = 'on'
      primary_conninfo      = 'host=localhost port=7432 user=pgadmin password=pgadmin'
      # Specifies a trigger file whose presence should cause streaming replication to
      # end (i.e., failover).
      trigger_file = '/var/lib/pgsql/9.0/shards/pg2/data/trigger'
      restore_command = 'cp /var/lib/pgsql/9.0/shards/pg2/archive/%f %p'
  • For pg2_s2
    • cd /var/lib/pgsql/9.0/shards/pg2_s2/data
    • cp ../../pg2_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg2_s1/data/recovery.conf .
  • For pg2_s3
    • cd /var/lib/pgsql/9.0/shards/pg2_s3/data
    • cp ../../pg2_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg2_s1/data/recovery.conf .

Setup Shard2 Slave File permissions

We repeat the setting up file permission to ensure any new files created have the same privileges.

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Start Shard2 Slave Instances

  • service pg2_s1 start
  • service pg2_s2 start
  • service pg2_s3 start
  • If startup failed or to ensure proper startup view these files.
    • For pg2_s1 see the latest log file in /var/lib/pgsql/9.0/shards/pg2_s1/data/pg_log/
    • For pg2_s2 see the latest log file in /var/lib/pgsql/9.0/shards/pg2_s2/data/pg_log/
    • For pg2_s3 see the latest log file in /var/lib/pgsql/9.0/shards/pg2_s3/data/pg_log/

Verify Streaming Replication processes for Shard2

  • To verify streaming replication run the following command:
    ps -ef | grep wal
  • If your setup is valid you should see:
    • 2 “wal write” process – one per master
    • 6 “wal receiver” processes – one for each slave
    • 6 “wal sender” processes – one for each slave

Test Replication for Shard2

  • In a terminal window start a psql session and connect to the Master:
    psql –username=pgadmin –port=7432 –dbname=shard_2
  • Execute the following SQL statements:
    CREATE TABLE test (test varchar(30));
    INSERT INTO test VALUES (‘Testing 1 2 3’);
  • In another terminal window start a psql session and connect to slave 1:
    psql –username=pgadmin –dbname=shard_2 –port=7433
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 2:
    psql –username=pgadmin –dbname=shard_2 –port=7434
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 3:
    psql –username=pgadmin –dbname=shard_2 –port=7435
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • Now in the Master psql session execute the statement:
    DROP TABLE test;
  • In any one of the slave psql sessions run:
    SELECT * from test;
  • The previous command will generate an error.

References

  1. http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
  2. http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use
  3. http://brandonkonkle.com/blog/2010/oct/20/postgres-9-streaming-replication-and-django-balanc/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: