PostgreSQL Administration

Friday, May 13, 2016

Streaming Replication Slots in PostgreSQL 9.4

The bellow Nine steps will give the configuration steps for Streaming Replication Slot with cascading in PPAS-9.5

Let’s go with hands on. Here I am going to choose one master and two slaves.

Master IP : 192.168.205.182 Port:5446

Slave IP : 192.168.205.181 Port:5447

Slave 1 IP : 192.168.205.181 Port:5448

Step 1

To configure streaming replication slot , edit postgresql.conf and pg_hba.conf as follows on the master server.

pg_hba.conf

Step 2

[enterprisedb@localhost ~]$ psql -p 5446

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

(0 rows)

edb=# select pg_create_physical_replication_slot('test_1');

pg_create_physical_replication_slot

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

(test_1,)

(1 row)

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

test_1 | | physical | | | f | 90372 | | | 1/37000140

(1 row)

Step 3

[enterprisedb@localhost ~]$ pg_basebackup -P -R -X stream -c fast -h 192.168.205.182 -U enterprisedb -p 5446 -D /opt/slave/

43626/43626 kB (100%), 1/1 tablespace

# These settings are ignored on a master server.

hot_standby = on # "on" allows queries during recovery

[enterprisedb@localhost ~]$ vi /opt/slave/recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb host=192.168.205.182 port=5446'

primary_slot_name= 'test_1'

trigger_file = '/tmp/trigger_test'

Step 4

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave/ start

server starting

[enterprisedb@localhost ~]$ 2016-04-30 03:46:31 PDT LOG: redirecting log output to logging collector process

2016-04-30 03:46:31 PDT HINT: Future log output will appear in directory "pg_log".

Type "help" for help.

edb=# select pg_is_in_recovery();

pg_is_in_recovery

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

(1 row)

[enterprisedb@localhost ~]$ psql -p 5446

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

test_1 | | physical | | | t | 90372 | | | 1/37000140

(1 row)

edb=# select * from pg_stat_replication;

-[ RECORD 1 ]----+---------------------------------

pid | 90372

usesysid | 10

usename | enterprisedb

application_name | walreceiver

client_addr | 192.168.205.181

client_hostname |

client_port | 51708

backend_start | 30-APR-16 03:46:31.452655 -07:00

backend_xmin |

state | streaming

sent_location | 1/37000220

write_location | 1/37000220

flush_location | 1/37000220

replay_location | 1/37000220

sync_priority | 0

sync_state | async

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

Step 5

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave/ stop -mf

waiting for server to shut down.... done

server stopped

[enterprisedb@localhost ~]$ psql -p 5446

psql.bin (9.5.0.5)

Type "help" for help.

edb=# insert into stest values(generate_series(1,10000000),('sample'));

INSERT 0 10000000

edb=# insert into stest values(generate_series(10000001,20000000),('sample'));

INSERT 0 10000000

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

100

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

102

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

105

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

107

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

108

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

Step 6

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave/ start

server starting

[enterprisedb@localhost ~]$ 2016-04-30 04:00:49 PDT LOG: redirecting log output to logging collector process

2016-04-30 04:00:49 PDT HINT: Future log output will appear in directory "pg_log".

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

102

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

106

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

107

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

After slave start on master files as follows :

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

Cascading Replication Configuration Using Slot

Step-1

[enterprisedb@localhost ~]$ pg_basebackup -P -R -X stream -c fast -h 192.168.205.182 -U enterprisedb -p 5446 -D /opt/slave1

1347443/1347443 kB (100%), 1/1 tablespace

[enterprisedb@localhost ~]$ psql -p 5447

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select pg_create_physical_replication_slot('test_2');

pg_create_physical_replication_slot

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

(test_2,)

(1 row)

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

(1 row)

[enterprisedb@localhost ~]$ vi /opt/slave1/recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb port=5447 password=adminedb'

primary_slot_name = 'test_2'

trigger_file = '/tmp/trigger_file'

Step-2

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave1/ start

server starting

[enterprisedb@localhost ~]$ 2016-04-30 04:25:43 PDT LOG: redirecting log output to logging collector process

2016-04-30 04:25:43 PDT HINT: Future log output will appear in directory "pg_log".

[enterprisedb@localhost ~]$ psql -p 5447

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

test_2 | | physical | | | t | 28785 | | | 1/A0000220

(1 row)

edb=# select * from pg_stat_replication;

-------+----------+--------------+------------------+-----------------+-----------------+-------------+----------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------

28785 | 10 | enterprisedb | walreceiver | 192.168.205.181 | | 56177 | 30-APR-16 04:25:43.722475 -07:00 | | streaming | 1/A0000220 | 1/A0000220 | 1/A0000220 | 1/A0000220 | 0 | async

(1 row)

edb=# \x

Expanded display is on.

edb=# select * from pg_stat_replication;

-[ RECORD 1 ]----+---------------------------------

pid | 28785

usesysid | 10

usename | enterprisedb

application_name | walreceiver

client_addr | 192.168.205.181

client_hostname |

client_port | 56177

backend_start | 30-APR-16 04:25:43.722475 -07:00

backend_xmin |

state | streaming

sent_location | 1/A0000220

write_location | 1/A0000220

flush_location | 1/A0000220

replay_location | 1/A0000220

sync_priority | 0

sync_state | async

edb=# select * from pg_replication_slots;

-[ RECORD 1 ]+-----------

slot_name | test_2

plugin |

slot_type | physical

datoid |

database |

active | t

active_pid | 28785

xmin |

catalog_xmin |

restart_lsn | 1/A0000220

Step-3

edb=# show port;

port

------

5446

(1 row

edb=# create database cascade;

CREATE DATABASE

edb=# show port ;

port

------

5447

(1 row)

edb=# \l+

List of databases

-----------+--------------+----------+-------------+-------------+-----+-------------------------------+---------+------------+--------------------------------------------

edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 1284 MB | pg_default |

(5 rows)|?

edb=# show port ;

port

------

5448

(1 row)

edb=# \l+

List of databases

-----------+--------------+----------+-------------+-------------+-----+-------------------------------+---------+------------+--------------------------------------------

edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 1284 MB | pg_default |

(5 rows)

Wednesday, May 11, 2016

Full Text Search (FTS) in PostgreSQL (Examples Included)

This post is aimed at providing only the information necessary to rapidly understand and deploy Full Text Search into your PostgreSQL environment.

1) Suppose we have a table named "fts" with the following schema :

id <serial>

body <text>

body_tsvector <tsvector>

CREATE TABLE fts (

id serial NOT NULL,

body text,

body_tsvector tsvector

)

2) Now let's suppose the following data is present within the table:

INSERT INTO fts (body) VALUES

('An artist cannot speak about his art any more than a plant can discuss horticulture.'),

('I like work; it fascinates me. I can sit and look at it for hours.'),

('Love is a great beautifier.');

3) Next, let's populate the body_tsvector column:

UPDATE fts SET body_tsvector = to_tsvector('english', body);

4) For speed's sake let's create an index on our body_tsvector column:

CREATE INDEX fts_body_tsvector_gin_idx ON fts USING GIN(body_tsvector);

5) Finally, let's create a trigger which automatically updates or populates our body_tsvector column whenever a row is updated or inserted:

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON fts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('body_tsvector', 'pg_catalog.english', 'body');

Now that we have a table which has been populated with data, has full text indexing set up, and will automatically update accordingly whenever a future update or insert occurs, let's run some tests to ensure full text search works as expected.

SELECT * FROM fts WHERE body_tsvector @@ plainto_tsquery('english', 'Discuss')

Should return: ID 1

SELECT * FROM fts WHERE body_tsvector @@ plainto_tsquery('english', 'Discuss LOVE')

Should return: IDs 1 and 3

SELECT * FROM fts WHERE body_tsvector @@ plainto_tsquery('english', 'Discuss') AND NOT (body_tsvector @@ plainto_tsquery('english', 'Love'))

Should return: ID 1

For more documentation, I'd recommend checking out the following URLs:

https://www.youtube.com/watch?v=tNG4gmQ3dV0

http://linuxgazette.net/164/sephton.html

http://www.postgresql.org/docs/9.1/static/textsearch.html

http://www.slideshare.net/billkarwin/full-text-search-in-postgresql

Slony-I Baisc Installation and Replication Setup Steps.

Source Installation:

Download the Slony-I source from http://slony.info/downloads/2.2/source/

#slony1-2.2.4.tar.bz2

#tar -xvf slony1-2.2.4.tar.bz2

#cd slony1-2.2.4

#./configure –prefix=/usr/local/pgsql/bin –with-pgconfigdir=/usr/local/pgsql/bin

#make

#make install

Step-1:

======

master=# select version();

version

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

PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit

(1 row)

masterdb=# \c postgres

You are now connected to database "postgres" as user "postgres".

postgres=# create database master;

CREATE DATABASE

postgres=# \c master

You are now connected to database "master" as user "postgres".

master=# create table s1(id int primary key,name varchar(10));

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "s1_pkey" for table "s1"

CREATE TABLE

master=# insert into s1 values(generate_series(1,10),('sample'));

INSERT 0 10

master=# select * from s1;

id | name

----+--------

1 | sample

2 | sample

3 | sample

4 | sample

5 | sample

6 | sample

7 | sample

8 | sample

9 | sample

10 | sample

(10 rows)

Step-2:

=====

slave=# select version();

version

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

PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit

(1 row)

postgres=# create database slave;

CREATE DATABASE

postgres=# \c slave

You are now connected to database "slave" as user "postgres".

slave=# create table s1(id int primary key,name varchar(10));

CREATE TABLE

Step-3:

=====

Setup replication, we need to create below scripts :

[postgres@localhost slon_test1]$ more initalize_BASICBLD.slonik

## Script for Initialization.

cluster name = basicbld;

node 1 admin conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb';

node 2 admin conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb';

init cluster (id = 1, comment = 'Primary node for slave');

#### Setting Store Nodes ###

store node (id = 2, event node = 1, comment = 'Slave Node For The Primary Node 1');

#### Storing all nodes in the Slony-I catalogs in schema _basicbld

store path(server = 1, client = 2, conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb');

store path(server = 2, client = 1, conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb');

[postgres@localhost slon_test1]$ more create_set_BASICBLD.slonik

## Script for Create_set

cluster name = basicbld;

node 1 admin conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb';

node 2 admin conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb';

#-----------------------------

# Creating sets for Tables

#-----------------------------

#---- Set 1 --------#

try { create set (id = 1, origin = 1, comment = 'Set 1 for shadow'); } on error { echo 'Failed...set 1'; exit 1;}

echo 'Set 1 ...created';

set add table (set id = 1, origin = 1, id = 1, full qualified name = 'public.s1', comment = 'Table public.s1 with primary key');

echo 'PKey table *** public.s1 *** added.';

[postgres@localhost slon_test1]$ more subscribe_set_BASICBLD.slonik

## Script for subscribe_set

cluster name = basicbld;

node 1 admin conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb';

node 2 admin conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb';

try { subscribe set (id = 1, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1;}

echo 'Set 1 subscribed to nodes 1';

[postgres@localhost slon_test1]$ more primary_start.sh

#!/bin/bash

# starting slon on Origin

# Change the binary location as per Origin Node

# Slony binaries/ip/clustername/conninfo according to node

DATE=$(date +%G%m%d)

SLONLOG="/tmp/primary_$DATE.log"

/usr/local/pgsql/bin/slon -s 1000 basicbld 'host=192.168.205.157 dbname=master user=postgres port=5432' >$SLONLOG 2>&1 &

exit

[postgres@localhost slon_test1]$ more slave_start.sh

#!/bin/bash

# starting slon on Subscriber

# Change Slony binaries/ip/clustername/conninfo according to

# Subscriber node.

DATE=$(date +%G%m%d)

SLONLOG="/tmp/slave_$DATE.log"

/usr/local/pgsql/bin/slon -s 1000 basicbld 'host=192.168.205.162 dbname=slave user=postgres port=5432' >$SLONLOG 2>&1 &

exit

Step-4

=====

Execute scripts in the below order:

[postgres@localhost slon_test1]$ /usr/local/pgsql/bin/slonik /tmp/slon_test1/initalize_BASICBLD.slonik

[postgres@localhost slon_test1]$ /usr/local/pgsql/bin/slonik /tmp/slon_test1/create_set_BASICBLD.slonik

/tmp/slon_test1/create_set_BASICBLD.slonik:13: Set 1 ...created

/tmp/slon_test1/create_set_BASICBLD.slonik:16: PKey table *** public.s1 *** added.

[postgres@localhost slon_test1]$ sh prymary_start.sh

[postgres@localhost slon_test1]$ sh slave_start.sh

[postgres@localhost slon_test1]$ /usr/local/pgsql/bin/slonik /tmp/slon_test1/subscribe_set_NEWSLN.slonik

/tmp/slon_test1/subscribe_set_NEWSLN.slonik:8: Set 2 subscribed to nodes 3

slave=# select * from s1;

id | name

----+--------

1 | sample

2 | sample

3 | sample

4 | sample

5 | sample

6 | sample

7 | sample

8 | sample

9 | sample

10 | sample

(10 rows)

Step-5

=====

master=# insert into s1 values(generate_series(11,15),('sample'));

INSERT 0 5

slave=# select * from s1;

id | name

----+--------

1 | sample

2 | sample

3 | sample

4 | sample

5 | sample

6 | sample

7 | sample

8 | sample

9 | sample

10 | sample

11 | sample

12 | sample

13 | sample

14 | sample

15 | sample

(15 rows)

For more information,Slony-I documentation is the best.

http://www.slony.info/adminguide/2.2/doc/adminguide/slony.pdf

And also you can go through the below, one of our senior Raghav blogspot will help you in short .

http://raghavt.blogspot.in/search?q=slony+installation

Friday, May 13, 2016

Streaming Replication Slots in PostgreSQL 9.4

Wednesday, May 11, 2016

Full Text Search (FTS) in PostgreSQL (Examples Included)

Slony-I Baisc Installation and Replication Setup Steps.

Step-5 =====

Step-5

=====