Home > Fedora 15 64 bit, Linux, PostgreSQL, Uncategorized > PostgreSQL 9.1.3 streaming replication at Amazon Web Services (EC2 , S3)

PostgreSQL 9.1.3 streaming replication at Amazon Web Services (EC2 , S3)

1. You need at least two nodes – Master and 1 slave
The solution is :

– Create ami image from the first instance, create second one and load the fresh new ami image with same security profile
– Check / add the port permissions at the security profile.
– change the PS1 at the two nodes to contain the strings node-1 and node-2 in a view be aware at which node are you in every single moment.

2. Follow the instructions from the official PostgreSQL wiki
– use the Amazon internal IP addresses in the replication setup
– Make sure the replication is working:

# Master
postgres@juan:5432::postgres=[Wed May 16 19:40:30 EDT 2012]# SELECT pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 0/9001280
(1 row)

Time: 161.324 ms

#Slave
postgres@kida:5432::postgres=[Wed May 16 19:00:42 EDT 2012]# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location 
------------------------------
 0/90011F0
(1 row)

Time: 165.566 ms

activating the XFS partition previously created (Add XFS partitiion to be used by PostgreSQL at AWS EC2)

# Master
postgres@juan:5432::postgres=[Wed May 16 19:00:28 EDT 2012]# create tablespace xfs location '/var/lib/pgsql9/xfs.data' ;
CREATE TABLESPACE
postgres@juan:5432::postgres=[Wed May 16 19:00:35 EDT 2012]# \db
               List of tablespaces
    Name    |  Owner   |         Location         
------------+----------+--------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 xfs        | postgres | /var/lib/pgsql9/xfs.data
(3 rows)

#The new table stace is replicated at the slave as well:
postgres@kida:5432::postgres=[Wed May 16 19:00:13 EDT 2012]# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

postgres@kida:5432::postgres=[Wed May 16 19:00:15 EDT 2012]# \db
               List of tablespaces
    Name    |  Owner   |         Location         
------------+----------+--------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 xfs        | postgres | /var/lib/pgsql9/xfs.data
(3 rows)

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: