PostgreSQL Installation and Configuration

PostgreSQL, often simply Postgres, is a powerful, open source object-relational database management system with an emphasis on extensibility and standards compliance. It is released under PostgreSQL License, a liberal open source license, similar to BSD or MIT licenses.

pgAdmin is the most popular and feature rich open source administration and development platform for PostgreSQL, as well as commercial and derived versions of PostgreSQL such as Postgres Plus Advanced Server and Greenplum database. It is free software released under PostgreSQL License.

Patch up the system by applying all updates.

# yum -y update

Configuring Kernel Parameters

Edit /etc/sysctl.conf file to set the kernel parameter values greater than or equal to the minimum value shown.

fs.file-max = 6815744

kernel.shmmax = 1073741824

kernel.shmall = 536870912

Run the following command to change the current kernel parameter values.

# /sbin/sysctl -p

Installing PostgreSQL Server 9.2 on a Red Hat Linux 6 System (64-bit)

1)      Requires root access on database server.

2)      Download PostgreSQL at http://www.enterprisedb.com/products-services-training/pgdownload

# chmod +x postgresql-9.2.8-1-linux-x64.run

# ./postgresql-9.2.8-1-linux-x64.run --mode text

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

Welcome to the PostgreSQL Setup Wizard.

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

Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

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

Please select a directory under which to store your data.

Data Directory [/opt/PostgreSQL/9.2/data]:

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

Please provide a password for the database superuser (postgres). A locked Unix

user account (postgres) will be created if not present.

Password :

Retype password :

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

Please select the port number the server should listen on.

Port [5432]:

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

Advanced Options

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]

[2] aa_DJ

[3] aa_DJ.iso88591

[4] aa_DJ.utf8

[5] aa_ER

[6] aa_ER@saaho

[7] aa_ER.utf8

[8] aa_ER.utf8@saaho

[9] aa_ET

[10] aa_ET.utf8

[11] af_ZA

[12] af_ZA.iso88591

[13] af_ZA.utf8

[14] am_ET

[15] am_ET.utf8

[16] an_ES

[17] an_ES.iso885915

[18] an_ES.utf8

…

[705] yo_NG

[706] yo_NG.utf8

[707] zh_CN

[708] zh_CN.gb2312

[709] zh_CN.utf8

[710] zh_HK.utf8

[711] zh_SG

[712] zh_SG.gb2312

[713] zh_SG.utf8

[714] zh_TW.euctw

[715] zh_TW.utf8

[716] zu_ZA

[717] zu_ZA.iso88591

[718] zu_ZA.utf8

Please choose an option [1] :

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

Setup is now ready to begin installing PostgreSQL on your computer.

Do you want to continue? [Y/n]: Y

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

Please wait while Setup installs PostgreSQL on your computer.

 Installing

 0% ______________ 50% ______________ 100%

 #########################################

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

Setup has finished installing PostgreSQL on your computer.

3)      The installation will configure postgres user with the home directory as /opt/PostgreSQL/9.2. Create a symbolic link. Otherwise, the service will fail to start.

# cd /etc/rc.d/init.d

# ln -s /etc/rc.d/init.d/postgresql-9.2 postgresql

4)      Configure the PostgreSQL server to start at boot.

# chkconfig postgresql on

# chkconfig --list postgresql

5)      Login as postgres and set environment in .bash_profile

$ su - postgres

$ vi .bash_profile

PGLIB=/ opt/PostgreSQL/9.2/lib

PGDATA=/opt/PostgreSQL/9.2/lib/data

export PGLIB PGDATA

Change the Default Data Directory (optional)

1)      Create a directory for the new location of the database files and change the ownership of the new location to allow access by the postgres user and group.

# mkdir -p /opt/pgsql/data

# chown -R postgres:postgres /opt/pgsql

2)      Modify to point the new location for data and log in /etc/rc.d/init.d/posgresql

PGDATA=/opt/pgsql/data

PGLOG=/opt/pgsql/pgstartup.log

3)      Initialize PostgreSQL database server. This creates a bunch of directories, a template directory and sets up the postgres configuration in the directory /opt/pgsql/.

$ /opt/PostgreSQL/9.2/bin/initdb -D /opt/pgsql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

fixing permissions on existing directory /opt/pgsql/data ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 32MB

creating configuration files ... ok

creating template1 database in /opt/pgsql/data/base/1 ... ok

initializing pg_authid ... ok

initializing dependencies ... ok

creating system views ... ok

loading system objects' descriptions ... ok

creating collations ... ok

creating conversions ... ok

creating dictionaries ... ok

setting privileges on built-in objects ... ok

creating information schema ... ok

loading PL/pgSQL server-side language ... ok

vacuuming database template1 ... ok

copying template1 to template0 ... ok

copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /opt/PostgreSQL/9.2/bin/postgres -D /opt/pgsql/data

or

    /opt/PostgreSQL/9.2/bin/pg_ctl -D /opt/pgsql/data -l logfile start

Configuration Settings

Update these setting in /opt/PostgreSQL/9.2/data/postgresql.conf. Ongoing tuning may be required based on resource utilization.

max_connection = 150

shared_buffers = 1024MB

wal_buffers = 16MB

checkpoint_segments = 128

checkpoint_completion_target = 0.9

wal_level = archive

archive_mode = on

Startup and Shutdown PostgreSQL Server

Execute any one of these commands to startup or shutdown PostgreSQL database server.

Shutdown commands:

$ /opt/PostgreSQL/9.2/bin/pg_ctl stop

# service postgresql stop

# /etc/rc.d/init.d/postgresql stop

Startup commands:

$ /opt/PostgreSQL/9.2/bin/postgres &

$ /opt/PostgreSQL/9.2/bin/pg_ctl start

# service postgresql start

# /etc/rc.d/init.d/postgresql start

Configure Listener

Set the listener parameters in the configuration file /opt/PostgreSQL/9.2/data/postgresql.conf.

listen_addresses = '*'

port = 5432

Configure Client Authentication

To grant access to remote users, enable network access in the host based authentication file /opt/PostgreSQL/9.2/data/pg_hba.conf. This rule allows all the users originating from the subnet 192.168.0.0/24.

host    all             all             192.168.0.0/24            md5

PostgreSQL installation layout

Directory/File	Contents
/opt/pgsql/data/postgresql.conf	Configuration file
/opt/pgsql/data/pg_hba.conf	Host Access file
/opt/pgsql/data/pg_ident.conf	Authentication/Identification file
/usr/local/pgsql/data	Data files
/opt/pgsql/data/pg_xlog/*	Write Ahead Log (WAL) files

Setup WAL Archiving

Set wal_level configuration parameter to archive or hot_standby

Set archive_mode configuration parameter to on.

Specify shell command in archive_command configuration parameter. In this string, %p is replaced by the path of the file to archive, while %f is replaced by the file name only.

archive_command = ‘test ! -f /opt/pgsql/arch/%f && cp %p /opt/pgsql/arch/%f’

restore_command = ‘cp /opt/pgsql/arch/%f %p’

Compressed Archive Logs

pg_compresslog removes unnecessary full_page_writes and trailing space from the WAL files.

archive_command = ‘pg_compresslog %p - | gzip > /opt/pgsql/arch/%f’

restore_command = ‘gunzip < /opt/pgsql/arch/%f | pg_decompresslog - %p’

archive_command Script

Using separate script is recommended to execute multiple commands in the archiving process.

archive_command = ‘backup.sh “%p” “%f”’

Online Backup

Connect as super user and begin the database in backup mode. Provide label to uniquely identify the backup operation.

SELECT pg_start_backup(‘label’);

Perform the backup using filesystem backup tool such as tar or cpio.

End the backup mode. This performs an automatic switch to next WAL segment.

SELECT pg_stop_backup();

The WAL segment files used during backup should be archived as part of normal database activity.

Here is the sample backup script.

$ psql -c “SELECT pg_start_backup(‘hot_backup’);”

$ tar -cf /opt/pgsql/backup.tar /opt/pgsql/data/

$ psql -c “SELECT pg_stop_backup();”

$ tar -rf /opt/pgsql/backup.tar /opt/pgsql/arch/

Custom Format Dump

The custom format dump will compress data as it writes to the output file. The tables can be restored selectively.

Backup a Database using custom format.

$ pg_dump -Fc dbname > dbname_$(date +%Y%m%d).bak                   # Compressed binary format

$ pg_dump -Ft dbname > dbname_$(date +%Y%m%d).tar                     # Compressed tarball

Restore Custom Format Dump

Connect to the database and directly restore into it.

$ pg_restore -Fc -d dbname dbname.bak               

$ pg_restore -Ft -d dbname dbname.tar                 

Create the database and restore into it.

$ pg_restore -Fc -C -d dbname dbname.bak                            

$ pg_restore -Ft -C -d dbname dbname.tar                              

Monitoring

Nagios provides complete monitoring of PostgreSQL databases including availability, database and table sizes, cache ratios, corruptions, and other key metrics.

NewRelic is a commercial SaaS application monitoring solution which offers PostgreSQL plugin maintained by EnterpriseDB.

psql Commands

\?: Get a full list of psql commands, including those not listed here.

\h: Get help on SQL commands. You can follow this with a specific command to get help with the syntax.

\q: Quit the psql program and exit to the Linux prompt.

\d: List available tables, views, and sequences in current database.

\du: List available roles

\dp: List access privileges

\dt: List tables

\l: List databases

\c: Connect to a different database. Follow this by the database name.

\password: Change the password for the username that follows.

\conninfo: Get information about the current database and connection.

Create a Database

$ su - postgres

$ psql

psql (9.3.2)

Type "help" for help.

postgres=# CREATE ROLE abitra LOGIN PASSWORD ‘passwd ‘;

postgres=# CREATE DATABASE empdb OWNER abitra ENCODING ‘UTF-8’;

Connect the Database

$ psql -h localhost -U abitra -d empdb

empdb=#

Create a Table in the Database

empdb=# CREATE TABLE emp (ename char(10), job char(9), deptno numeric(2));

CREATE TABLE

Insert Data into a Table

empdb=# INSERT INTO emp values ('SMITH', 'CLERK', 20);

INSERT 0 1

empdb=# INSERT INTO emp VALUES ('ALLEN', 'SALESMAN', 30);

INSERT 0 1

Select Data from a Table

empdb=# SELECT * FROM emp;

   ename    |    job    | deptno

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

 SMITH      | CLERK     |     20

 ALLEN      | SALESMAN  |     30

(2 rows)

empdb=# \q

List the Databases

$ psql -l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  

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

 empdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     

    +

                     |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     

    +

                     |          |          |             |             | postgres=CTc/postgres

(4 rows)

List the schemas

$ psql

psql (9.3.2)

Type "help" for help.

postgres=# \c empdb

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

empdb=# \d

        List of relations

 Schema | Name | Type  |  Owner  

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

 public | emp  | table | postgres

(1 row)

empdb=# \q

Create a Super User

$ psql

psql (9.3.2)

Type "help" for help.

postgres=# CREATE ROLE abitra LOGIN PASSWORD 'passwd' SUPERUSER;

Create the User and Grant the Privileges

postgres=# CREATE USER scott WITH PASSWORD 'tiger';

CREATE ROLE

postgres=# \c empdb

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

empdb=# GRANT SELECT ON emp to scott;

empdb=# GRANT INSERT ON emp to scott;

empdb=# GRANT ALL PRIVILEGES ON DATABASE empdb TO scott;

empdb=# \q

Delete a User

$ dropuser scott

Delete a Database.

$ destroydb empdb

$