This post describes how to run multiple PostgreSQL 9.2 Instances on one server in CentOS 6/RHEL 6/Fedora flavors. I am assuming that you have a running postgresql engine on the server already. If not check this post on how to install postgresql server that I wrote earlier and install the first/main instance. Now that you have a running database engine(I will call it first instance) you can start slicing second instance on the same server. As most of my posts I will divide this post into multiple steps for easier understanding and implementation.

This post helps you install, setup and benchmark pgbouncer connection pooling for PostgreSQL 9.2 on CentOS 6/RedHat/Fedora based systems. pgbouncer is one of the PostgreSQL connection poolers. There are other connection poolers available for PostgreSQL. Any enduser applications can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections. The purpose of connection poolers in general is to lower the performance impact of opening new connections to PostgreSQL(or Other) databases. pgbouncer can connection pool in three ways, which are

This post explains how to setup replication with Postgres 9.2 on CentOS 6/Redhat EL6/Fedora in Master slave configuration. This is also often called as streaming replication. Before you dig into this topic more, I would suggest going through my other post of implementing point in time recovery (PITR) using postgres 9.2. I am assuming that you have at-least two PostgreSQL 9.2 servers for establishing replication between those two database servers. If you don’t have experience installing PostgreSQL 9.2 on CentOS or Redhat please read my other blog post here on how to install PostgreSQL 9.2 server and get it up and running in no time. First we need to work on master database server and we will eventually move on to slave server configuration.

This post describes how to do point in time recovery with PostgreSQL 9.2 : PITR. Point in time recovery (PITR) simply means restoring data upto certain point in time. This method uses low level API backups, if you want to implement PITR using pg_basebackup utility then read my other post here. Before you proceed any further I would assume that you already have a server ready to implement PITR. If you do not have a PostgreSQL database server then I would suggest to read my other post about installing and configuring PostgreSQL 9.2 database server. Now we have a database server running, before we jump into implementation I want you to see your postgres data directory structure, which should look like the following:

This post explain how to do point in time recovery: PITR using pg_basebackup with PostgreSQL 9.2. This is a bit easier than using PITR with low level API backups as explained in this post. This is very nice powerful utility provided along with PostgreSQL installation for taking low-level backups.

1. Backup Process

As a good measure install all updates to this CentOS 6 server. Before you continue further. This step is optional.

Postgres Repo:

pgdg is the repo that postgresql can be downloaded and installed from. Here is where you download it from. Identify which version of rpm you want to download for installing PostgreSQL database server. Right now I am picking PostgreSQL 9.2 for CentOS [CentOS 6 – x86_64] as this is the latest version at this time. Now that we have identified which version we want to download, right click on that hyperlink and click "copy link address".

The ~/.psqlrc file determines the behavior of psql interactive command line client. Just like bashrc, psql client utility attempts to read and execute commands from the system-wide psqlrc file and the user’s ~/.psqlrc file before starting up. (On Windows, the user’s startup file is named %APPDATA%\postgresql\psqlrc.conf.)