Cascading Replication

First, the good news.  Setting up a chain of replicas has now become fairly simple even for non-DBAs.  Here's the basic steps:

1. Edit pg_hba.conf to enable replication connections.
2. Edit postgresql.conf to turn on the various replication options.
3. Restart the master.
4. Set up passwordless access to the master from the first replica.
5. Use pg_basebackup to snapshot the master from the replica.
6. Create a recovery.conf pointing at the master.
7. Start the replica.
8. Run pg_basebackup on the second replica.
9. Edit recovery.conf to point at the first replica.
10. Start the second replica.
11. Repeat steps 8-10 for as many replicas you want to chain.

Moving Postgresql Data Folder

We had a old server which ran out space for partition where postgres was running… we had to move the data folder to a bigger partition… and here is how I did it…

Step 0:  Stop the Postgres if running

$service postgresql stop

Step 1 :  Copy  the whole  data/  folder  to  new parition

say  we have  /usr/local/pgsql/data   and wants to move to /home2/ partition

$cp -R /usr/local/pgsql/data  /home2/postgres/data

Step 2 :  Modify the Startup script to point to correct data directory

In  /etc/init.d/postgresql  file,  change the value of  PGDATA  variable to new location  which is /home2/postgres/data

Step 3 : Start the db server

$ service postgresql start

MOVE A POSTGRESQL DATABASE TO A DIFFERENT TABLESPACE

Moving a complete database to a different tablespace involves 3 steps :

1. You have to tell PostgreSQL to create new tables and indexes for the database in the new tablespace. This can be done with :

   1	ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace];

2. Then you have to move each table in that database to the new tablespace with :

   1	ALTER TABLE "[table_name]" SET TABLESPACE "[new_tablespace]";

   This line of SQL code is needed for each table in the database.
3. And that’s not all, because the indexes also have to be moved. That’s done with :

   1	ALTER INDEX "[index_name]" SET TABLESPACE "[new_tablespace]";

   This line of SQL code is needed for each index in the database.

Where does PostgreSQL store the database?

To see where the data directory is, use this query.

show data_directory;

To see all the run-time parameters, use

show all;

You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query.

SELECT * FROM pg_tablespace;

Postgresql: Move database to new tablespace

ALTER DATABASE name SET TABLESPACE new_tablespace

Excerpt from the docs:

... changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected.

Postgres password authentication fails

As shown in the latest edit, the password is valid until 1970, which means it's currently invalid. This explains the error message which is the same as if the password was incorrect.

Reset the validity with:

ALTER USER postgres VALID UNTIL 'infinity';

In a recent question, another user had the same problem with user accounts and PG-9.2:

PostgreSQL - Password authentication fail after adding group roles

So apparently there is a way to unintentionally set a bogus password validity to the Unix epoch (1st Jan, 1970, the minimum possible value for the abstime type). Possibly, there's a bug in PG itself or in some client tool that would create this situation.

Streaming Replication

Streaming Replication (SR) provides the capability to continuously ship and apply the WAL XLOG records to some number of standby servers in order to keep them current.

This feature was added to PostgreSQL 9.0. The discussion below is a developer oriented one that contains some out of date information. Users of this feature should use the documentation for the feature or a setup tutorial instead:

• PostgreSQL Streaming Replication Documentation - this documentation is for the latest version, but provides links you can use to look up the docs for your version.

• Binary Replication Tutorial provides an introduction to using this replication feature.

• The related but independent Hot Standby feature.

Contents  [hide]  1 Developer and historical details on the project 2 Usage 2.1 Users Overview 2.2 Restrictions 2.3 How to Use 3 Todo 3.1 v9.0 3.1.1 Committed 3.2 v9.1 3.2.1 Synchronization capability 3.3 Future release

Developer and historical details on the project

SR was developed for inclusion in PostgreSQL 9.0 by NTT OSS Center. The lead developer is Masao Fujii. Synchronous Log Shipping Replication Presentation introduces the early design of the feature.

Usage

Users Overview

• Log-shipping
  • XLOG records generated in the primary are periodically shipped to the standby via the network.
  • In the existing warm standby, only records in a filled file are shipped, what's referred to as file-based log-shipping. In SR, XLOG records in partially-filled XLOG file are shipped too, implementing record-based log-shipping. This means the window for data loss in SR is usually smaller than in warm standby, unless the warm standby was also configured for record-based shipping (which is complicated to setup).
  • The content of XLOG files written to the standby are exactly the same as those on the primary. XLOG files shipped can be used for a normal recovery and PITR.
• Multiple standbys
  • More than one standby can establish a connection to the primary for SR. XLOG records are concurrently shipped to all these standbys. The delay/death of a standby does not harm log-shipping to other standbys.
  • The maximum number of standbys can be specified as a GUC variable.
• Continuous recovery
  • The standby continuously replays XLOG records shipped without using pg_standby.
  • XLOG records shipped are replayed as soon as possible without waiting until XLOG file has been filled. The combination of Hot Standby and SR would make the latest data inserted into the primary visible in the standby almost immediately.
  • The standby periodically removes old XLOG files which are no longer needed for recovery, to prevent excessive disk usage.
• Setup
  • The start of log-shipping does not interfere with any query processing on the primary.
  • The standby can be started in various conditions.
    • If there are XLOG files in archive directory and restore_command is supplied, at first those files are replayed. Then the standby requests XLOG records following the last applied one to the primary. This prevents XLOG files already present in the standby from being shipped again. Similarly, XLOG files in pg_xlog are also replayed before starting log-shipping.
    • If there is no XLOG files on the standby, the standby requests XLOG records following the starting XLOG location of recovery (the redo starting location).
• Connection settings and authentication
  • A user can configure the same settings as a normal connection to a connection for SR (e.g., keepalive, pg_hba.conf).
• Activation
  • The standby can keep waiting for activation as long as a user likes. This prevents the standby from being automatically brought up by failure of recovery or network outage.
• Progress report
  • The primary and standby report the progress of log-shipping in PS display.
• Graceful shutdown
  • When smart/fast shutdown is requested, the primary waits to exit until XLOG records have been sent to the standby, up to the shutdown checkpoint record.

Restrictions

• Synchronous log-shipping
  • By default, SR supports operates in asynchronous manner, so the commit command might return a "success" to a client before the corresponding XLOG records are shipped to the standby. To enable synchronous replication, seeSynchronous Replication
• Replication beyond timeline
  • A user has to get a fresh backup whenever making the old standby catch up.
• Clustering
  • Postgres doesn't provide any clustering feature.

How to Use

• 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.

$ $EDITOR postgresql.conf

listen_addresses = '192.168.0.10'

$ $EDITOR pg_hba.conf

# The standby server must connect with a user that has replication privileges.
host  replication  replication  192.168.0.20/22  trust

• 4. Set up the streaming replication related parameters on the primary 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 is not necessary.
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

• 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 -ac ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"

• 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.
• 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.0.10 port=5432 user=postgres'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/path_to/trigger'

# 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 /path_to/archive/%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 thepg_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.
• 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 6th.
• If you have more than one slave, promoting one will break the other(s). Update their recovery.conf settings to point to the new master, set recovery_target_timeline to 'latest', scp/rsync the pg_xlog directory, and restart the slave.

Show replication status in PostgreSQL

This is a small tip to view the replication status of slave postgresql servers. The simplest way to see the replication state on a master server is executing this query:

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

This query can output can be like this:

postgres=# select client_addr, state, sent_location
postgres-# write_location, flush_location, replay_location, sync_priority from pg_stat_replication;
 client_addr |   state   | write_location | flush_location | replay_location | sync_priority
-------------+-----------+----------------+----------------+-----------------+---------------
 10.0.2.184  | streaming | AB/416D178     | AB/416D178     | AB/416D178      |             0
(1 row)

If the slave is up in hot standby mode, you can tell the time in seconds the delay of transactions applied on the slave with this query:

select now() - pg_last_xact_replay_timestamp() AS replication_delay;

This is a possible output:

postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
-------------------
 00:00:08.057668
(1 row)

In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing (this is generally the same limitation as MySQL's SHOW SLAVE STATUS output).

Backup & Restore Database In PostgreSQL (Pg_dump,Pg_restore)

1)Backup data with pg_dump

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/test.backup" test01

To list all of the available options of pg_dump , please issue following command.

pg_dump -?
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name
2) Restore data with pg_restore
pg_restore -i -h localhost -p 5432 -U postgres -d test01 -v 
"/usr/local/backup/test.backup"
To list all of the available options of pg_restore , please issue following command.
pg_restore -?
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode

Postgres Replication with Archiving on Master.

Both postgres machine are on 9.0.12

 

When I set wal_level = 'hot_standby' on master and hot_standby = 'on' in slave. 

It does do streaming repl but no archiving on master?

 

# master

wal_level = 'hot_standby'

archive_mode = on

archive_command = '../pgscripts/archive.sh %p %f'

 

# - Streaming Replication -

max_wal_senders = 3

wal_keep_segments = 8

 

# slave

wal_level = 'hot_standby'

archive_mode = on

archive_command = '../pgscripts/archive.sh %p %f'

checkpoint_segments = 8

hot_standby = on

 

master log:

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

LOG:  connection received: host=[local]

LOG:  connection authorized: user=pgsql database=postgres

LOG:  connection received: host=192.168.4.223 port=56788

LOG:  replication connection authorized: user=repuser host=192.168.4.223 port=56788

 

slave log:

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

LOG:  entering standby mode

LOG:  consistent recovery state reached at 6/B2000078

LOG:  database system is ready to accept read only connections

LOG:  streaming replication successfully connected to primary

LOG:  connection received: host=[local]

LOG:  connection authorized: user=pgsql database=postgres



Create 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.4.233 port=5432 user=repuser password=changeme'

 

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

# end (i.e., failover).

trigger_file = '/var/db/pgsql_backup/postgresql.trigger.standby'

 

# 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 /var/db/pgsql_backup/archive/%f "%p"'

 

# archive_cleanup_command = 'pg_archivecleanup /var/db/pgsql_backup/archive %r'

How to find (log) slow queries in PostgreSQL 8.x, 9.x

One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen more than 100x performance improvements just from a one word change in a query. So how you can identify which queries are running slowly in your system? That is easy, just turn on the logging in your PostgreSQL database. This method has been tested in versions 8.3 and 8.4, but should apply to even 9.x!

Enable slow query logging in PostgreSQL

• Open the file postgresql.conf file in your favorite text editor.
  Note: If you are having trouble finding the file, run the command: find / -name postgresql.conf
• Look for the line:
  #log_min_duration_statement = -1and replace it with:
  log_min_duration_statement = 100
• Save the file and reload the PostgreSQL configuration (Don’t worry, it won’t interrupt any active connections)
  service postgresql reload
• Go to the pg_log folder and open the latest log file. (i.e. /var/lib/pgsql/data/pg_log/)

Setting the value to 100 will log every query that takes 100ms or longer to execute. Depending on the number of slow queries you have, this may be overwhelming, so consider increasing this value.

If you need some assistance in optimizing your slow queries, feel free to post them in the comments section below!