Tag Archives: replication

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

Postgres Replication Alternatives

31 Oct

Replication Related Concepts

  • Clustering vs Replication
    • Used interchangeably and similar concepts in the DBMS World
    • Clustering in the general sense is distributing load across many servers that appear to an outside application as one server
    • Replication in the general sense involves one master server handling mostly writes and one or more slaves handling reads only. A more advanced variation is that of the master-master configuration, which allows to scale writes as well by having several servers writing at the same time.
    • There are several definitions of clustering, but it can generally be viewed as something that has membership, messaging, redundancy, and automatic failover capabilities. Replication, in contrast, is simply a way to send messages (data) from one server to another.
  • Replication Types
    • Synchronous
      • Postgres 9.1 built-in Synchronous support
      • PgPool II
    • Asynchronous
      • Postgres 9.0 Streaming built-in
      • Slony-1
      • Bucardo
  • Replication Modes
    • Master-Master
    • Master-Slave
    • Statement-Based-Middleware
  • Connection Pooling
  • Load Balancing
  • High Availability
    • Automatic Failover
    • Manual Failover
  • Parallel Queries
  • Performance
    • Asynchronous is Faster
    • Synchronous is Slower

Repmgr vs PgPoolII comparison

Feature Repmgr PgPool II
Proxy Process/Service No intermediary Process/Service. Clients Access Database Directly Clients connect to PgPool II Service which delegates to Postgres servers.
Replication Type Postgres 9.1 Synchronous Replication PgPool II Synchronous Replication
Replication Mode Master Slave Statement Based Middleware
Connection Pooling None. But can be used with Pgbouncer Built-in Pgbouncer
Load Balancing None. PgBouncer can use a TCP load balancer such as LVS or HAProxy Built-In Load Balancing
High Availability Yes. Custom Scripts Available and Monitoring Daemon (repmgrd) Yes. Automatic Failover scripts available
Parallel Queries None available out of the box. Avaialble out of the box
Performance Synchronous is generally slower than Asynchronous. Synchronous (using a custom protocol.)

Conclusion

  • PgPoolII has more features out-of-the-box but Repmgr seems a lot more flexible.
  • RepMgr + pgbouncer should give us all the features we are looking for.

References