PostgreSQL DBA Routine Tasks

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. Some general PostgreSQL limits are included in the table below.

Maximum Database SizeUnlimited
Maximum Table Size32 TB
Maximum Row Size1.6 TB
Maximum Field Size1 GB
Maximum Rows per TableUnlimited
Maximum Columns per Table250 - 1600 depending on column types
Maximum Indexes per TableUnlimited

PostgreSQL has won praise from its users and industry recognition, including the Linux New Media Award for Best Database System and five time winner of the The Linux Journal Editors' Choice Award for best DBMS.

Featureful and Standards Compliant

PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard. It has full support for subqueries (including subselects in the FROM clause), read-committed and serializable transaction isolation levels. And while PostgreSQL has a fully relational system catalog which itself supports multiple schemas per database, its catalog is also accessible through the Information Schema as defined in the SQL standard.

Data integrity features include (compound) primary keys, foreign keys with restricting and cascading updates/deletes, check constraints, unique constraints, and not null constraints.

It also has a host of extensions and advanced features. Among the conveniences are auto-increment columns through sequences, and LIMIT/OFFSET allowing the return of partial result sets. PostgreSQL supports compound, unique, partial, and functional indexes which can use any of its B-tree, R-tree, hash, or GiST storage methods.

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ranked B+-trees and many others. It also provides an interface which allows both the creation of custom data types as well as extensible query methods with which to search them. Thus, GiST offers the flexibility to specify what you store, how you store it, and the ability to define new ways to search through it --- ways that far exceed those offered by standard B-tree, R-tree and other generalized search algorithms.

GiST serves as a foundation for many public projects that use PostgreSQL such as OpenFTS and PostGIS. OpenFTS (Open Source Full Text Search engine) provides online indexing of data and relevance ranking for database searching. PostGIS is a project which adds support for geographic objects in PostgreSQL, allowing it to be used as a spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension.

Other advanced features include table inheritance, a rules systems, and database events. Table inheritance puts an object oriented slant on table creation, allowing database designers to derive new tables from other tables, treating them as base classes. Even better, PostgreSQL supports both single and multiple inheritance in this manner.

The rules system, also called the query rewrite system, allows the database designer to create rules which identify specific operations for a given table or view, and dynamically transform them into alternate operations when they are processed.

The events system is an interprocess communication system in which messages and events can be transmitted between clients using the LISTEN and NOTIFY commands, allowing both simple peer to peer communication and advanced coordination on database events. Since notifications can be issued from triggers and stored procedures, PostgreSQL clients can monitor database events such as table updates, inserts, or deletes as they happen.

Highly Customizable

PostgreSQL runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL. Included with its standard function library are hundreds of built-in functions that range from basic math and string operations to cryptography and Oracle compatibility. Triggers and stored procedures can be written in C and loaded into the database as a library, allowing great flexibility in extending its capabilities. Similarly, PostgreSQL includes a framework that allows developers to define and create their own custom data types along with supporting functions and operators that define their behavior. As a result, a host of advanced data types have been created that range from geometric and spatial primitives to network addresses to even ISBN/ISSN (International Standard Book Number/International Standard Serial Number) data types, all of which can be optionally added to the system.

Just as there are many procedure languages supported by PostgreSQL, there are also many library interfaces as well, allowing various languages both compiled and interpreted to interface with PostgreSQL. There are interfaces for Java (JDBC), ODBC, Perl, Python, Ruby, C, C++, PHP, Lisp, Scheme, and Qt just to name a few.

Best of all, PostgreSQL's source code is available under a liberal open source license: the PostgreSQL License. This license gives you the freedom to use, modify and distribute PostgreSQL in any form you like, open or closed source. Any modifications, enhancements, or changes you make are yours to do with as you please. As such, PostgreSQL is not only a powerful database system capable of running the enterprise, it is a development platform upon which to develop in-house, web, or commercial software products that require a capable RDBMS.

System Catalogs pg_aggregate pg_am pg_amop pg_amproc pg_attrdef pg_attribute pg_authid pg_auth_members pg_cast pg_class pg_constraint pg_collation pg_conversion pg_database pg_db_role_setting pg_default_acl pg_depend pg_description pg_enum pg_extension pg_foreign_data_wrapper pg_foreign_server pg_foreign_table pg_index pg_inherits pg_language pg_largeobject pg_largeobject_metadata pg_namespace pg_opclass pg_operator pg_opfamily pg_pltemplate pg_proc pg_rewrite pg_seclabel pg_shdepend pg_shdescription pg_statistic pg_tablespace pg_trigger pg_ts_config pg_ts_config_map pg_ts_dict pg_ts_parser pg_ts_template pg_type pg_user_mapping System Views pg_available_extension_versions pg_cursors pg_group pg_indexes pg_locks pg_prepared_statements pg_prepared_xacts pg_roles pg_rules pg_seclabels pg_settings pg_shadow pg_stats pg_tables pg_timezone_abbrevs pg_timezone_names pg_user pg_user_mappings pg_views

pgpool vs slony

posted Feb 24, 2012, 4:27 PM by Sachchida Ojha   [ updated Feb 24, 2012, 4:32 PM ]

Slony-I is a "master to multiple slaves" replication system for PostgreSQL supporting cascading (e.g. - a node can feed another node which feeds another node...) and failover. The big picture for the development of Slony-I is that it is a master-slave replication system that includes all features and capabilities needed to replicate large databases to a reasonably limited number of slave systems.

Slony-I is a system designed for use at data centers and backup sites, where the normal mode of operation is that all nodes are available.

PostgreSQL 9.0 includes streaming replication, which, for a number of use cases, is likely to be simpler and more convenient than Slony-I.

There are, however, three characteristic kinds of cases where you'll need something like Slony-I, where the built-in WAL-based replication won't work:

You need to interact between PostgreSQL versions. Slony (and similar systems like Londiste and Bucardo) can cope with having nodes running different versions of PostgreSQL.

  • WAL-based replication requires that all databases use identical versions of PostgreSQL, running on identical architectures.
  • You only want to replicate parts of the changes that are going on.WAL-based replication duplicates absolutely everything.
  • You need for there to be extra behaviours taking place on subscribers, for instance, populating cache management information.WAL-based replication duplicates absolutely everything, and nothing extra that changes data can run on a WAL-based replica.

What is pgpool-II

pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client. It is licensed under BSD license. It provides the following features.

Connection Pooling pgpool-II saves connections to the PostgreSQL servers, and reuse them whenever a new connection with the same properties (i.e. username, database, protocol version) comes in. It reduces connection overhead, and improves system's overall throughput.

Replication pgpool-II can manage multiple PostgreSQL servers. Using the replication function enables creating a realtime backup on 2 or more physical disks, so that the service can continue without stopping servers in case of a disk failure.

Load Balance If a database is replicated, executing a SELECT query on any server will return the same result. pgpool-II takes an advantage of the replication feature to reduce the load on each PostgreSQL server by distributing SELECT queries among multiple servers, improving system's overall throughput. At best, performance improves proportionally to the number of PostgreSQL servers. Load balance works best in a situation where there are a lot of users executing many queries at the same time.

Limiting Exceeding Connections There is a limit on the maximum number of concurrent connections with PostgreSQL, and connections are rejected after this many connections. Setting the maximum number of connections, however, increases resource consumption and affect system performance. pgpool-II also has a limit on the maximum number of connections, but extra connections will be queued instead of returning an error immediately.

Parallel Query Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time. Parallel query works the best when searching large-scale data.

pgpool-II talks PostgreSQL's backend and frontend protocol, and relays a connection between them. Therefore, a database application (frontend) thinks that pgpool-II is the actual PostgreSQL server, and the server (backend) sees pgpool-II as one of its clients. Because pgpool-II is transparent to both the server and the client, an existing database application can be used with pgpool-II almost without a change to its sources.

What is Pgpool ? how it is implemented?

posted Feb 24, 2012, 5:59 AM by Sachchida Ojha   [ updated Feb 24, 2012, 4:25 PM ]

Pgpool is a connection pooling/replication server for PostgreSQL. pgpool-II latest version  pgpool-II 3.0.5 released on October 31, 2011 Download

Hot Standby/Streaming Replication Vs Warm Standby/Log Shipping

posted Feb 23, 2012, 6:15 PM by Sachchida Ojha

Hot Standby/Streaming Replication is available as of PostgreSQL 9.0 and provides asynchronous binary replication to one or more standbys. Standbys may also become hot standbys meaning they can be queried as a read-only database. This is the fastest type of replication available as WAL data is sent immediately rather than waiting for a whole segment to be produced and shipped.

Warm Standby/Log Shipping is a HA solution which 'replicates' a database cluster to an archive or a warm (can be brought up quickly, but not available for querying) standby server. Overhead is very low and it's easy to set up. This is a simple and appropriate solution if all you care about is continuous backup and short failover times.

Object level permission in PostgreSQL

posted Feb 20, 2012, 6:33 AM by Sachchida Ojha

Every object (tables, views and sequences) have an owner, which is the person that created it. The owner, or a superuser, can set permissions on the object. Permissions are made up of a user or group name and a set of rights. These rights described in the table below.

 Privilege short name Description
 SELECT r Can read data from the object.
 INSERT a Can insert data into the object.
 UPDATE w Can change data in the object.
 DELETE d Can delete data from the object.
 RULE R  Can create a rule on the table
 REFERENCES r  Can create a foreign key to a table. Need this on both sides of the key.
 TRIGGER t Can create a trigger on the table.
 TEMPORARY T Can create a temporary table.
 EXECUTE X Can run the function.
USAGE U Can use the procedural language.
 ALL arwdRxt All appropriate privileges. For tables, this equates to arwdRxt

You can apply these privileges to users, groups or a special target called PUBLIC, which is any user on the system.

Viewing privileges

You can view permissions using the \z command in psql.

You can use \d to view the owner.
dbaref=# \dp

Adding privileges

You can assign privileges using the GRANT command. GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

The WITH GRANT OPTION allows you give the person you are granting the privileges the ability to grant that privilege themselves. We can give bob the ability to make any changes to the data in suppliers using: GRANT INSERT, UPDATE, DELETE ON TABLE suppliers TO bob;

Removing privileges

You can also remove privileges using the REVOKE which has the same syntax as the GRANT.REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

GRANT OPTION FOR allows you to remove the ability to grant privileges to others, and not the privileges themselves. Suppose you want to remove privileges from bob, and anyone he has granted it to, we can use the CASCADE option. REVOKE INSERT UPDATE DELETE ON TABLE suppliers FROM bob CASCASE

Column Level Privileges

PostgreSQL doesn't directly support privileges at the column level but you can fake the, using views. To do this, you create a view with all the columns you want that person to see and grant them privileges to view that view.

Changing Ownership

It is possible to change the ownership of objects using the ALTER TABLE: ALTER TABLE suppliers OWNER TO bob;

This can be time consuming to do if you have a lot of tables. A quicker, but possibly dodgy way to fix this is to use the following untested SQL command. You need to set relowner to the sysid of the new owner, which you can find by checking pg_shadow. UPDATE pg_class SET relowner = 100 WHERE pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = 'public';

postmaster -- PostgreSQL multiuser database server

posted Feb 19, 2012, 6:40 PM by Sachchida Ojha

postmaster is the PostgreSQL multiuser database server. In order for a client application to access a database it connects (over a network or locally) to a running postmaster. The postmaster then starts a separate server process ("postgres") to handle the connection. The postmaster also manages the communication among server processes.

[root@usha data]# ls -l
total 92
drwx------. 5 postgres postgres  4096 Feb 19 13:00 base
drwx------. 2 postgres postgres  4096 Feb 19 21:14 global
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_clog
-rw-------. 1 postgres postgres  4232 Feb 19 13:00 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Feb 19 13:00 pg_ident.conf
drwx------. 2 postgres postgres  4096 Feb 19 13:30 pg_log
drwx------. 4 postgres postgres  4096 Feb 19 13:00 pg_multixact
drwx------. 2 postgres postgres  4096 Feb 19 13:30 pg_notify
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_serial
drwx------. 2 postgres postgres  4096 Feb 19 21:33 pg_stat_tmp
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_subtrans
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_tblspc
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_twophase
-rw-------. 1 postgres postgres     4 Feb 19 13:00 PG_VERSION
drwx------. 3 postgres postgres  4096 Feb 19 13:00 pg_xlog
-rw-------. 1 postgres postgres 19146 Feb 19 13:00 postgresql.conf
-rw-------. 1 postgres postgres    71 Feb 19 13:30 postmaster.opts
-rw-------. 1 postgres postgres    81 Feb 19 13:30
[root@usha data]# cat
  5432001  48234497
[root@usha data]#


postmaster [-A 0 | 1 ] [-B nbuffers] [-c name=value] [-d debug-level] [-D datadir] [-F] [-h hostname] [-i] [-k directory] [-l] [-N max-connections] [-o extra-options] [-p port] [-S] [--name=value] [-n | -s]

By default the postmaster starts in the foreground and prints log messages to the standard error stream. In practical applications the postmaster should be started as a background process, perhaps at boot time.

One postmaster always manages the data from exactly one database cluster. A database cluster is a collection of databases that is stored at a common file system location (the "data area"). More than one postmaster process can run on a system at one time, so long as they use different data areas and different communication ports (see below). A data area is created with initdb.

When the postmaster starts it needs to know the location of the data area. The location must be specified by the -D option or the PGDATA environment variable; there is no default. Typically, -D or PGDATA points directly to the data area directory created by initdb.

Configuring PostgreSQL

posted Feb 19, 2012, 6:23 PM by Sachchida Ojha

postgresql.conf is the main configuration file for the PostgreSQL DBMS. It will be located in your data directory.
[root@usha local]# cd /var/lib/pgsql/9.1/data/

[root@usha data]# ls -ltr
total 92
-rw-------. 1 postgres postgres     4 Feb 19 13:00 PG_VERSION
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_twophase
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_tblspc
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_serial
drwx------. 4 postgres postgres  4096 Feb 19 13:00 pg_multixact
-rw-------. 1 postgres postgres 19146 Feb 19 13:00 postgresql.conf
-rw-------. 1 postgres postgres  1636 Feb 19 13:00 pg_ident.conf
-rw-------. 1 postgres postgres  4232 Feb 19 13:00 pg_hba.conf
drwx------. 3 postgres postgres  4096 Feb 19 13:00 pg_xlog
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_subtrans
drwx------. 2 postgres postgres  4096 Feb 19 13:00 pg_clog
drwx------. 5 postgres postgres  4096 Feb 19 13:00 base
-rw-------. 1 postgres postgres    81 Feb 19 13:30
drwx------. 2 postgres postgres  4096 Feb 19 13:30 pg_notify
-rw-------. 1 postgres postgres    71 Feb 19 13:30 postmaster.opts
drwx------. 2 postgres postgres  4096 Feb 19 13:30 pg_log
drwx------. 2 postgres postgres  4096 Feb 19 13:30 global
drwx------. 2 postgres postgres  4096 Feb 19 21:11 pg_stat_tmp
[root@usha data]#

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
# This file consists of lines of the form:
#   name = value
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                              s   = seconds
#                GB = gigabytes                                min = minutes
#                                                                      h   = hours
#                                                                       d   = days


# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'           # write an extra PID file
                                        # (change requires restart)


# - Connection Settings -

#listen_addresses = 'localhost'        # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all
                    # (change requires restart)
#port = 5432                # (change requires restart)
max_connections = 100            # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3    # (change requires restart)
#unix_socket_directory = ''        # (change requires restart)
#unix_socket_group = ''            # (change requires restart)
#unix_socket_permissions = 0777        # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off                # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''            # defaults to the computer name
                    # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min        # 1s-600s
#ssl = off                # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
                    # (change requires restart)
#ssl_renegotiation_limit = 512MB    # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres'        # (Kerberos only)
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0        # TCP_KEEPCNT;
                    # 0 selects the system default


# - Memory -

shared_buffers = 32MB            # min 128kB
                    # (change requires restart)
#temp_buffers = 8MB            # min 800kB
#max_prepared_transactions = 0        # zero disables the feature
                    # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB                # min 64kB
#maintenance_work_mem = 16MB        # min 1MB
#max_stack_depth = 2MB            # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000        # min 25
                    # (change requires restart)
#shared_preload_libraries = ''        # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms        # 0-100 milliseconds
#vacuum_cost_page_hit = 1        # 0-10000 credits
#vacuum_cost_page_miss = 10        # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms            # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0        # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1        # 1-1000. 0 disables prefetching


# - Settings -

#wal_level = minimal            # minimal, archive, or hot_standby
                    # (change requires restart)
#fsync = on                # turns forced synchronization on or off
#synchronous_commit = on        # synchronization level; on, off, or local
#wal_sync_method = fsync        # the default is the first option
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync (default on Linux)
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
#full_page_writes = on            # recover from partial page writes
#wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
                    # (change requires restart)
#wal_writer_delay = 200ms        # 1-10000 milliseconds

#commit_delay = 0            # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000

# - Checkpoints -

#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min        # range 30s-1h
#checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s        # 0 disables

# - Archiving -

#archive_mode = off        # allows archiving to be done
                # (change requires restart)
#archive_command = ''        # command to use to archive a logfile segment
#archive_timeout = 0        # force a logfile segment switch after this
                # number of seconds; 0 disables


# - Master Server -

# These settings are ignored on a standby server

#max_wal_senders = 0        # max number of walsender processes
                # (change requires restart)
#wal_sender_delay = 1s        # walsender cycle time, 1-10000 milliseconds
#wal_keep_segments = 0        # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0    # number of xacts by which cleanup is delayed
#replication_timeout = 60s    # in milliseconds; 0 disables
#synchronous_standby_names = ''    # standby servers that provide sync rep
                # comma-separated list of application_name
                # from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a master server

#hot_standby = off            # "on" allows queries during recovery
                    # (change requires restart)
#max_standby_archive_delay = 30s    # max delay before canceling queries
                    # when reading WAL from archive;
                    # -1 allows indefinite delay
#max_standby_streaming_delay = 30s    # max delay before canceling queries
                    # when reading streaming WAL;
                    # -1 allows indefinite delay
#wal_receiver_status_interval = 10s    # send replies at least this often
                    # 0 disables
#hot_standby_feedback = off        # send info from standby to prevent
                    # query conflicts


# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0            # measured on an arbitrary scale
#random_page_cost = 4.0            # same scale as above
#cpu_tuple_cost = 0.01            # same scale as above
#cpu_index_tuple_cost = 0.005        # same scale as above
#cpu_operator_cost = 0.0025        # same scale as above
#effective_cache_size = 128MB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5            # range 1-10
#geqo_pool_size = 0            # selects default based on effort
#geqo_generations = 0            # selects default based on effort
#geqo_selection_bias = 2.0        # range 1.5-2.0
#geqo_seed = 0.0            # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100    # range 1-10000
#constraint_exclusion = partition    # on, off, or partition
#cursor_tuple_fraction = 0.1        # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8        # 1 disables collapsing of explicit
                    # JOIN clauses


# - Where to Log -

log_destination = 'stderr'        # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on            # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'        # directory where log files are written,
                    # can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log'    # log file name pattern,
                    # can include strftime() escapes
#log_file_mode = 0600            # creation mode for log files,
                    # begin with 0 to use octal notation
log_truncate_on_rotation = on        # If on, an existing log file with the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
log_rotation_age = 1d            # Automatic rotation of logfiles will
                    # happen after that time.  0 disables.
log_rotation_size = 0            # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

#silent_mode = off            # Run server silently.
                    # DO NOT USE without syslog or
                    # logging_collector
                    # (change requires restart)

# - When to Log -

#client_min_messages = notice        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   log
                    #   notice
                    #   warning
                    #   error

#log_min_messages = warning        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic

#log_min_error_statement = error    # values in order of decreasing detail:
                     #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                     #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic (effectively off)

#log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
                    # and their durations, > 0 logs only
                    # statements running at least this number
                    # of milliseconds

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default        # terse, default, or verbose messages
#log_hostname = off
#log_line_prefix = ''            # special values:
                    #   %a = application name
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %i = command tag
                    #   %e = SQL state
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '
#log_lock_waits = off            # log lock waits >= deadlock_timeout
#log_statement = 'none'            # none, ddl, mod, all
#log_temp_files = -1            # log temporary files equal or larger
                    # than the specified size in kilobytes;
                    # -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'


# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_functions = none            # none, pl, all
#track_activity_query_size = 1024     # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
#log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
#autovacuum_max_workers = 3        # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 50    # min number of row updates before
                    # vacuum
#autovacuum_analyze_threshold = 50    # min number of row updates before
                    # analyze
#autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit


# - Statement Behavior -

#search_path = '"$user",public'        # schema names
#default_tablespace = ''        # a tablespace name, '' uses the default
#temp_tablespaces = ''            # a list of tablespace names, '' uses
                    # only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0            # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex'            # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'

# - Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = '(defaults to server environment setting)'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                    # abbreviations.  Currently, there are
                    #   Default
                    #   Australia
                    #   India
                    # You can create your own file in
                    # share/timezonesets/.
#extra_float_digits = 0            # min -15, max 3
#client_encoding = sql_ascii        # actually, defaults to database
                    # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'            # locale for system error message
                    # strings
lc_monetary = 'en_US.UTF-8'            # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'            # locale for number formatting
lc_time = 'en_US.UTF-8'                # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

# - Other Defaults -

#dynamic_library_path = '$libdir'
#local_preload_libraries = ''


#deadlock_timeout = 1s
#max_locks_per_transaction = 64        # min 10
                    # (change requires restart)
# Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64    # min 10
                    # (change requires restart)


# - Previous PostgreSQL Versions -

#array_nulls = on
#backslash_quote = safe_encoding    # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on

# - Other Platforms and Clients -

#transform_null_equals = off


#exit_on_error = off                # terminate session on any error?
#restart_after_crash = on            # reinitialize after backend crash?


#custom_variable_classes = ''        # list of custom variable class names
[root@usha data]#

Authentication and Authorisation

posted Feb 19, 2012, 6:02 PM by Sachchida Ojha   [ updated Feb 19, 2012, 6:02 PM ]

PostgreSQL has two levels of authorisation, one at the database level, called host based authentication, and one at a finer level on tables, views and sequences.
Host-Based Authentication using pg_hba.conf

The host-based authentication is controlled by the pg_hba.conf file and defines which users can connect to which database and how they can connect to it. The file is a list of declarations, which are searched in order until one of the lines match. They list the access method, the database they are trying to connect to, the user trying to connect and the authentication method being used.

The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is quoted. Records cannot be continued across lines.

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

A record can have one of the seven formats


local      database  user  auth-method  [auth-option]
host       database  user  CIDR-address  auth-method  [auth-option]
hostssl    database  user  CIDR-address  auth-method  [auth-option]
hostnossl  database  user  CIDR-address  auth-method  [auth-option]
host       database  user  IP-address  IP-mask  auth-method  [auth-option]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-option]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-option]
Access methods

There are three different access methods:

This is for a user connecting via the unix socket on the local machine. A line for this method will be in the form:local DATABASE USER METHOD [OPTION]



hostnossl, hostssl

This is for users connecting over a non-encrypted or an encrypted TCP/IP connection using SSL. This is so that you can treat secure and non-secure connections differently. For example you might be happy to have clear text passwords over SSL, but only allow MD5 over non-secure connections. hostnossl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] hostnossl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] hostssl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] hostssl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]

You can list several databases by separating them by commas. There are two special database names, all and sameuser. all allows the person to connect to all databases on the server. sameuser allows the user to connect to a database with the same name as the user connecting. You can also supply a filename which lists databases they can connect to by using @filename where filename is a file in the same directory as the pg_hba.conf.

You can also list several users by separating them by commas. You can specify groups by prefixing the name with a +. Again you can use a filename with users in by using @filename where filename is a file in the same directory as pg_hba.conf. There is the special username all, which matches any user.

Authentication Methods


This method allows any user to connect without a password. This should be avoided unless you know what you are doing as it could be a security risk.


This is the reverse of trust, as it rejects any one. This is particularly useful where you want to enable access to a range of addresses, but want to block a particular host in that range. host sales alice reject host sales alice md5


This method allows someone to connect providing they have given the correct password for their user in the pg_shadow table. If the password field in that table is null, then they will be rejected. The password is sent in cleartext, so you probably only want to enable this for hostssl connections.


This is like password, except it is encrypted with the trivial unix crypt encryption. This is not a very strong encryption, so I suggest you don't use this.


This is like crypt and password, except it uses the stronger MD5 to encrypt the password. If you have to use non-ssl connections, I recommend you using this method.


With network connections, this method uses the ident protocol (RFC1413) to check which user on the remote system owns the network connection being used to talk to the server. This is easy to spoof, so you shouldn't reply on this for unsecured networks. I would recommend against it on all networks.

With local connections, it uses the unix user connecting to the unix socket and is much more secure. This allows local users to connect without a password.

This is the only method that requires an option, which is the name of a map in pg_ident.conf, which maps remote users to postgresql users. The format of pg_ident.conf is: map unixuser postgresuser
This allows you to have the same remote user map to different postgres users when they connect to different databases,

There is a special map name called sameuser, which uses the same remote username for the postgresql name.

krb4, krb5

These allow you to use kerberos authentication.


This method allows you to authenticate users against the local system's pam (pluggable authentication modules) subsystem.

For local connections, I would recommend ident, password, crypt or md5. For hostssl connections any of the password methods will work, but md5 is preferable. For host and hostnossl, I can only recommend md5 and hostssl should be used in preference to host and hostnossl. local all postgres ident sameuser hostssl all postgres md5 local sameuser all ident sameuser hostssl sameuser all md5 hostssl sales alice md5

Installing PostgreSQL 9.1 on Fedora 15

posted Feb 19, 2012, 10:45 AM by Sachchida Ojha

1.  Download the Software

2. Go to the download directory and verify the file.

[root@usha ~]# cd /home/oracle/Desktop/postgresql
[root@usha postgresql]# ls -ltr
total 8
-rw-r--r--. 1 oracle oinstall 5125 Feb 19 12:47 pgdg-fedora91-9.1-4.noarch.rpm
[root@usha postgresql]# chmod 755 pgdg-fedora91-9.1-4.noarch.rpm

3. Install the RPM Distribution

[root@usha postgresql]# rpm -ivh pgdg-fedora91-9.1-4.noarch.rpm
warning: pgdg-fedora91-9.1-4.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                ########################################### [100%]
   1:pgdg-fedora91          ########################################### [100%]

4. Install PostgreSQL

a) List available packages
[root@usha postgresql]# yum list postgres*
Loaded plugins: langpacks, presto, refresh-packagekit
adobe-linux-x86_64                                       |  951 B     00:00    
adobe-linux-x86_64/primary                               | 1.2 kB     00:00    
pgdg91                                                   | 2.8 kB     00:00    
pgdg91/primary_db                                        |  49 kB     00:00    
pgdg91/group                                             |  674 B     00:00    
updates/metalink                                         |  16 kB     00:00    
updates                                                  | 4.5 kB     00:00    
updates/primary_db                                       | 6.6 MB     00:53    
adobe-linux-x86_64                                                          2/2
Available Packages
postgresql.i686                           9.0.6-1.fc15                   updates
postgresql.x86_64                         9.0.6-1.fc15                   updates
postgresql-contrib.x86_64                 9.0.6-1.fc15                   updates
postgresql-dbi-link.noarch                2.0.0-6.fc15                   fedora
postgresql-devel.i686                     9.0.6-1.fc15                   updates
postgresql-devel.x86_64                   9.0.6-1.fc15                   updates
postgresql-docs.x86_64                    9.0.6-1.fc15                   updates
postgresql-ip4r.x86_64                    1.05-2.fc15                    fedora
postgresql-jdbc.x86_64                    9.0.801-4.fc15                 updates
postgresql-libs.i686                      9.0.6-1.fc15                   updates
postgresql-libs.x86_64                    9.0.6-1.fc15                   updates
postgresql-odbc.x86_64                    09.00.0200-2.fc15              fedora
postgresql-odbcng.i686                    0.99.101-0.5.test1.fc15        fedora
postgresql-odbcng.x86_64                  0.99.101-0.5.test1.fc15        fedora
postgresql-pgpool-II.i686                 2.3.1-2.fc15                   fedora
postgresql-pgpool-II.x86_64               2.3.1-2.fc15                   fedora
postgresql-pgpool-II-devel.i686           2.3.1-2.fc15                   fedora
postgresql-pgpool-II-devel.x86_64         2.3.1-2.fc15                   fedora
postgresql-pgpool-ha.noarch               1.1.0-8.fc12                   fedora
postgresql-pgpoolAdmin.noarch             2.2-2.fc12                     fedora
postgresql-plparrot.x86_64                0.04-9.fc15                    updates
postgresql-plperl.x86_64                  9.0.6-1.fc15                   updates
postgresql-plpython.x86_64                9.0.6-1.fc15                   updates
postgresql-plruby.x86_64                  0.5.3-3.fc12                   fedora
postgresql-plruby-doc.x86_64              0.5.3-3.fc12                   fedora
postgresql-pltcl.x86_64                   9.0.6-1.fc15                   updates
postgresql-server.x86_64                  9.0.6-1.fc15                   updates
postgresql-table_log.x86_64               0.4.4-10.fc15                  fedora
postgresql-test.x86_64                    9.0.6-1.fc15                   updates
postgresql-upgrade.x86_64                 9.0.6-1.fc15                   updates
postgresql91.x86_64                       9.1.2-1PGDG.f15                pgdg91
postgresql91-contrib.x86_64               9.1.2-1PGDG.f15                pgdg91
postgresql91-debuginfo.x86_64             9.1.2-1PGDG.f15                pgdg91
postgresql91-devel.x86_64                 9.1.2-1PGDG.f15                pgdg91
postgresql91-docs.x86_64                  9.1.2-1PGDG.f15                pgdg91
postgresql91-libs.x86_64                  9.1.2-1PGDG.f15                pgdg91
postgresql91-odbc.x86_64                  09.00.0310-1PGDG.f15           pgdg91
postgresql91-odbc-debuginfo.x86_64        09.00.0310-1PGDG.f15           pgdg91
postgresql91-plperl.x86_64                9.1.2-1PGDG.f15                pgdg91
postgresql91-plpython.x86_64              9.1.2-1PGDG.f15                pgdg91
postgresql91-pltcl.x86_64                 9.1.2-1PGDG.f15                pgdg91
postgresql91-server.x86_64                9.1.2-1PGDG.f15                pgdg91
postgresql91-test.x86_64                  9.1.2-1PGDG.f15                pgdg91
postgresql_autodoc.noarch                 1.40-2.fc14                    fedora

b ) Install the PostgreSQL

[root@usha postgresql]# yum install postgresql91-server.x86_64
Loaded plugins: langpacks, presto, refresh-packagekit
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql91-server.x86_64 0:9.1.2-1PGDG.f15 will be installed
--> Processing Dependency: postgresql91 = 9.1.2-1PGDG.f15 for package: postgresql91-server-9.1.2-1PGDG.f15.x86_64
--> Processing Dependency: for package: postgresql91-server-9.1.2-1PGDG.f15.x86_64
--> Running transaction check
---> Package postgresql91.x86_64 0:9.1.2-1PGDG.f15 will be installed
---> Package postgresql91-libs.x86_64 0:9.1.2-1PGDG.f15 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

 Package                  Arch        Version                 Repository   Size
 postgresql91-server      x86_64      9.1.2-1PGDG.f15         pgdg91      3.2 M
Installing for dependencies:
 postgresql91             x86_64      9.1.2-1PGDG.f15         pgdg91      910 k
 postgresql91-libs        x86_64      9.1.2-1PGDG.f15         pgdg91      185 k

Transaction Summary
Install       3 Package(s)

Total download size: 4.2 M
Installed size: 19 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 4.2 M
(1/3): postgresql91-9.1.2-1PGDG.f15.x86_64.rpm           | 910 kB     00:05    
(2/3): postgresql91-libs-9.1.2-1PGDG.f15.x86_64.rpm      | 185 kB     00:00    
(3/3): postgresql91-server-9.1.2-1PGDG.f15.x86_64.rpm    | 3.2 MB     00:25    
Total                                           136 kB/s | 4.2 MB     00:31    
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : postgresql91-libs-9.1.2-1PGDG.f15.x86_64                     1/3
  Installing : postgresql91-9.1.2-1PGDG.f15.x86_64                          2/3
  Installing : postgresql91-server-9.1.2-1PGDG.f15.x86_64                   3/3

  postgresql91-server.x86_64 0:9.1.2-1PGDG.f15                                 

Dependency Installed:
  postgresql91.x86_64 0:9.1.2-1PGDG.f15                                        
  postgresql91-libs.x86_64 0:9.1.2-1PGDG.f15                                   


5) Initialize the database

[root@usha postgresql]# cd /var/lib/pgsql/9.1/data/
[root@usha data]# service postgresql-9.1 initdb
Initializing database:                                     [  OK  ]

6) Set the database for automatic startup - If you want PostgreSQL to startup automatically on reboot

[root@usha data]# chkconfig postgresql-9.1 on

7) Start the database

[root@usha data]# service postgresql-9.1 start
Starting postgresql-9.1 (via systemctl):                   [  OK  ]
[root@usha data]#

start : start the database
stop : stop the database
restart : stop/start the database; used to read changes to core configuration files
reload : reload pg_hba.conf file while keeping database running


To remove everything:
yum erase postgresql-9.1*

1-8 of 8