10 Minutes to Postgresql: June 2011

Dynamic Partition creation and insertion

CREATE TABLE footest (

foo_id integer NOT NULL,

blaa_id integer NOT NULL,

blaa_num integer NOT NULL,

foo_num integer NOT NULL,

createdatetime timestamp with time zone DEFAULT now()

);

CREATE TABLE footest_1to50k (

CHECK ( foo_id >= 1 AND foo_id <= 50000 )

) INHERITS (footest);

CREATE UNIQUE INDEX by_blaa_num_1to50k ON footest_1to50k USING btree (foo_id, blaa_id, blaa_num);

CREATE UNIQUE INDEX pk_footest_1to50k ON footest_1to50k USING btree (foo_id, foo_num);

CREATE TABLE footest_51to100k (

CHECK ( foo_id >= 50001 AND foo_id <= 100000 )

) INHERITS (footest);

CREATE UNIQUE INDEX by_blaa_num_51to100k ON footest_51to100k USING btree (foo_id, blaa_id, blaa_num);

CREATE UNIQUE INDEX pk_footest_51to100k ON footest_51to100k USING btree (foo_id, foo_num);

CREATE TABLE footest_101to150k (

CHECK ( foo_id >= 100001 AND foo_id <= 150000 )

) INHERITS (footest);

CREATE UNIQUE INDEX by_blaa_num_101to150k ON footest_101to150k USING btree (foo_id, blaa_id, blaa_num);

CREATE UNIQUE INDEX pk_footest_101to150k ON footest_101to150k USING btree (foo_id, foo_num);

--===================CREATING TRIGGER & TRIGGER FUNCTION PARTITION TABLES========================================

CREATE TRIGGER insert_footest_trigger

BEFORE INSERT ON footest

FOR EACH ROW EXECUTE PROCEDURE footest_insert_trigger();

CREATE OR REPLACE FUNCTION footest_insert_trigger()

RETURNS trigger AS $$

DECLARE

part integer;

event_part1 integer;

event_part2 integer;

table_name varchar;

BEGIN

part = (((NEW.foo_id/50000))*50);

event_part1=0;

event_part2=0;

event_part1= part + 1;

event_part2= part + 50 ;

table_name='footest_' || event_part1 || 'to' || event_part2 || 'k';

IF not exists(select * from pg_class where relname = table_name) THEN

EXECUTE 'CREATE TABLE ' || table_name || '(CHECK ( foo_id >=' || event_part1 * 1000 || 'AND foo_id <=' || event_part2 * 1000 || ' )) INHERITS (footest)' ;

EXECUTE 'CREATE UNIQUE INDEX by_blaa_num_' || event_part1 || 'to' || event_part2 || 'k ON ' || table_name ||' USING btree (foo_id, blaa_id, blaa_num)';

EXECUTE 'CREATE UNIQUE INDEX pk_footest_' || event_part1 || 'to' || event_part2 || 'k ON ' || table_name ||' USING btree (foo_id, foo_num)';

EXECUTE 'GRANT ALL ON TABLE ' || table_name || ' TO foorole, postgres';

EXECUTE 'GRANT SELECT ON TABLE ' || table_name || ' TO blarole';

END IF;

EXECUTE 'INSERT INTO ' || table_name ||' VALUES (($1).*)' USING NEW ;

RETURN NULL;

END;

LANGUAGE plpgsql;

1. Install postgres in the primary and standby server as usual. This requires only configure, make and make install.

2. Create the initial database cluster in the primary server as usual, using initdb.

3. Set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary.

Let’s assume primary (master) db server: 192.168.20.152,

secondary (Slave) db server: 192.168.20.113

On Secondary (slave) Server configure the below

Postgresql.conf

listen_addresses = ‘*’

pg_hba.conf

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all all 127.0.0.1/32 trust

# IPv6 local connections:

host all all ::1/128 trust

host all all 192.168.20.152/32 trust

host all all 192.168.20.113/32 trust

host all all 192.168.20.0/16 md5

host replication repl 192.168.20.0/16 md5

Note: make sure the primary server has a user “repl” with super user ability.

4. Set up the streaming replication related parameters on the primary server.

Primary (Master) Server:

$ $EDITOR postgresql.conf

# To enable read-only queries on a standby server, wal_level must be set to

# "hot_standby". But you can choose "archive" if you never connect to the

# server in standby mode.

wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.

max_wal_senders = 5

# To prevent the primary server from removing the WAL segments required for

# the standby server before shipping them, set the minimum number of segments

# retained in the pg_xlog directory. At least wal_keep_segments should be

# larger than the number of segments generated between the beginning of

# online-backup and the startup of streaming replication. If you enable WAL

# archiving to an archive directory accessible from the standby, this may

# not be necessary.

wal_keep_segments = 32

# Enable WAL archiving on the primary to an archive directory accessible from

# the standby. If wal_keep_segments is a high enough number to retain the WAL

# segments required for the standby server, this may not be necessary.

archive_mode = on

archive_command = 'cp %p /mnt/wallogs/archive/%f'

Note:

· /mnt/wallogs/archive is a local directory on Primary (master) server, Wal logs are copied into /mnt/wallogs/archive by postgres process

· /mnt/wallogs should be mounted and accessible on secondary (slave) server.

/mnt/wallogs can be on NFS or AFS or any storage media, which is easily accessible by both Master and Slave boxes.

5. Start postgres on the primary server.

6. Make a base backup by copying the primary server's data directory to the standby server.

$ psql -c "SELECT pg_start_backup('label', true)"

$ rsync -a ${PGDATA}/ postgres@dbpg9-test-01:/var/lib/pgsql/9.0/data/ --exclude postmaster.pid

$ psql -c "SELECT pg_stop_backup()"

Note: exclude postmaster.pid else you might have to rebuild everything from scratch.

7. Set up replication-related parameters, connections and authentication in the standby server like the primary, so that the standby might work as a primary after failover.

Make sure to setup pg_hba.conf file same as above:

8. Enable read-only queries on the standby server. But if wal_level is archive on the primary, leave hot_standby unchanged (i.e., off).

$ $EDITOR postgresql.conf

hot_standby = on

9. Create a recovery command file in the standby server; the following parameters are required for streaming replication.

$ $EDITOR recovery.conf

# Note that recovery.conf must be in $PGDATA directory.

# Specifies whether to start the server as a standby. In streaming replication,

# this parameter must to be set to on.

standby_mode          = 'on'

# Specifies a connection string which is used for the standby server to connect

# with the primary.

primary_conninfo      = ''host=192.168.20.152 port=5432 user=repl password=r3plic@t3'

# Specifies a trigger file whose presence should cause streaming replication to

# end (i.e., failover).

trigger_file = '/mnt/wallogs/trigger_dir/trigger_pg'

# Specifies a command to load archive segments from the WAL archive. If

# wal_keep_segments is a high enough number to retain the WAL segments

# required for the standby server, this may not be necessary. But

# a large workload can cause segments to be recycled before the standby

# is fully synchronized, requiring you to start again from a new base backup.

restore_command = ' cp /mnt/wallogs/primary/%f "%p"'

10. Start postgres in the standby server. It will start streaming replication.

11. You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location / pg_last_xlog_replay_location on the standby, respectively.

$ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)

pg_current_xlog_location

--------------------------

0/2000000

(1 row)

$ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host)

pg_last_xlog_receive_location

-------------------------------

0/2000000

(1 row)

$ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host)

pg_last_xlog_replay_location

------------------------------

0/2000000

(1 row)

12. You can also check the progress of streaming replication by using ps command.

# The displayed LSNs indicate the byte position that the standby server has

# written up to in the xlogs.

[primary] $ ps -ef | grep sender

postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000

[standby] $ ps -ef | grep receiver

postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/2000000

How to do failover

Create the trigger file in the standby after the primary fails.

How to stop the primary or the standby server

Shut down it as usual (pg_ctl stop).

How to restart streaming replication after failover

Repeat the operations from 6th; making a fresh backup, some configurations and starting the original primary as the standby. The primary server doesn't need to be stopped during these operations.

Note: Make sure the secondary server has proper recovery.conf file with right ip address to access the archive.

How to restart streaming replication after the standby fails

Restart postgres in the standby server after eliminating the cause of failure.

How to disconnect the standby from the primary

Create the trigger file in the standby while the primary is running. Then the standby would be brought up.

How to re-synchronize the stand-alone standby after isolation

Shut down the standby as usual. And repeat the operations from 6^th step.

Source:

http://wiki.postgresql.org/wiki/Streaming_Replication

10 Minutes to Postgresql

Monday, June 6, 2011

Partitioning dynamic table creation and Insertion

Streaming Replication