postgresql.conf controls the core settings of the PostgreSQL server instance as well as default settings for new databases. Many settings—such as sorting memory—can be overriden at the database, user, session, and even function levels for PostgreSQL versions higher than 8.3.

Details on how to tune this can be found at Tuning Your PostgreSQL Server.

An easy way to check the current settings you have is to query the pg_settings view, as we demonstrate in Example 2-2. Details of the various columns of information and what they mean are described in pg_settings.

SELECT name, context , unit 
 , setting , boot_val , reset_val 
FROM pg_settings
WHERE name in('listen_addresses','max_connections','shared_buffers','effective_cache_size', 'work_mem', 'maintenance_work_mem')
ORDER BY context,name;

name                 |  context   | unit | setting | boot_val  | reset_val
---------------------+------------+------+---------+-----------+-----------
listen_addresses     | postmaster |      | *       | localhost | *
max_connections      | postmaster |      | 100     | 100       | 100
shared_buffers       | postmaster | 8kB  | 4096    | 1024      | 4096
effective_cache_size | user       | 8kB  | 16384   | 16384     | 16384
maintenance_work_mem | user       | kB   | 16384   | 16384     | 16384
work_mem             | user       | kB   | 1024    | 1024      | 1024

We point out the following parameters as ones you should pay attention to in postgresql.conf. Changing their values requires a service restart:

The following three settings are important, too, and take effect without requiring a restart, but require at least a reload, as described in Reload the Configuration Files.

The above settings can also be set at the database, function, or user level. For example, you might want to set work_mem higher for a power user who runs sophisticated queries. Similarly, if you have a sort-intensive function, you could raise the work_mem just for it.

The pg_hba.conf controls which and how users can connect to PostgreSQL databases. Changes to the pg_hba.conf require a reload or a server restart to take effect. A typical pg_hba.conf looks like this:

# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host	all		all		127.0.0.1/32	ident 
# IPv6 local connections:
host	all		all	::1/128 	trust
host	all		all	192.168.54.0/24 	md5
hostssl  all	all	0.0.0.0/0	md5
# Allow replication connections from localhost, by a user with the 
# replication privilege.
#host	replication	postgres	127.0.0.1/32	trust
#host	replication	postgres	::1/128	trust

For each connection request, postgres service checks the pg_hba.conf file in order from the top down. Once a rule granting access is encountered, processing stops and the connection is allowed. Should the end of the file be reached without any matching rules, the connection is denied. A common mistake people make is to not put the rules in order. For example, if you put 0.0.0.0/0 reject before you put 127.0.0.1/32 trust, local users won’t be able to connect, even though you have a rule allowing them to do so.

Many, but not all changes, to configuration files require restarting the postgres service. Many changes take effect by performing a reload of the configuration. Reloading doesn’t affect active connections. Open up a command line and follow these steps to reload:

pg_ctl reload -D your_data_directory_here

If you have PostgreSQL installed as a service in Redhat EL or CentOS, you can do:

service postgresql-9.1 reload

where postgresql-9.1 is the name of your service.

You can also log in as a super user on any database and run this SQL statement:

SELECT pg_reload_conf();

You can also do this from pgAdmin, refer to Editing postgresql.conf and pg_hba.conf from pgAdmin.

postgres is an account that is created when you first initialize the PostgreSQL data cluster. It has a companion database called postgres. Before you do anything else, you should login as this user via psql or pgAdmin and create other users. pgAdmin has a graphical section for creating user roles, but if you were to do it using standard SQL data control language (DCL), you would execute an SQL command as shown in Example 2-3.

CREATE ROLE leo LOGIN PASSWORD 'lion!king'
CREATEDB VALID UNTIL 'infinity';

The 'infinity' is optional and assumed if not specified. You could instead put in a valid date at which you want the account to expire.

If you wanted to create a user with super rights, meaning they can cause major destruction to your database cluster and can create what we call untrusted language functions, you would create such a user as shown in Example 2-4. You can only create a super user if you are a super user yourself.

CREATE ROLE regina LOGIN PASSWORD 'queen!penultimate' 
 SUPERUSER VALID UNTIL '2020-10-20 23:00';

As you can see, we don’t really want our queen to reign forever, so we put in a timestamp when her account will expire.

Group roles are generally roles that have no login rights but have other roles as members. This is merely a convention. There is nothing stopping you from creating a role that can both login and can contain other roles.

We can create a group role with this SQL DCL statement:

CREATE ROLE jungle INHERIT;

And add a user or other group role to the group with this statement:

GRANT jungle TO leo;

One quirky thing about PostgreSQL is the ability to define a role that doesn’t allow its member roles to inherit its rights. The concept comes into play when you define a role to have member roles. You can designate that members of this role don’t inherit rights of the role itself. This is a feature that causes much confusion and frustration when setting up groups, as people often forget to make sure that the group role is marked to allow its permissions as inheritable.

A template database is, as the name suggests, a database that serves as a template for other databases. In actuality, you can use any database as template for another, but PostgreSQL allows you to specifically flag certain databases as templates. The main difference is that a database marked as template can’t be deleted and can be used by any user having CREATEDB rights (not just superuser) as a template for their new database. More details about template databases are described in the PostgreSQL manual Managing Template Databases.

The template1 database that is used as the default when no template is specified, doesn’t allow you to change encodings. As such, if you want to create a database with an encoding and collation different from your default, or you installed extensions in template1 you don’t want in this database, you may want to use template0 instead.

CREATE DATABASE mydb TEMPLATE template0;

If we wanted to make our new database a template, we would run this SQL statement as a super user:

UPDATE pg_database SET datistemplate=true WHERE datname='mydb';

This would allow other users with CREATEDB rights to use this as a template. It will also prevent the database from being deleted.

Schemas are a logical way of partitioning your database into mini-containers. You can divide schemas by functionality, by users, or by any other attribute. Aside from logical partitioning, they provide an easy way for doling out rights. One common practice is to install all contribs and extensions, covered in Extensions and Contribs into a separate schema and give rights to all users of a database.

To create a schema called contrib in a database, we connect to the database and run this SQL:

CREATE SCHEMA contrib;

The default search_path defined in postgresql.conf is "$user",public. This means that if there is a schema with the same name as the logged in user, then all non-schema qualified objects will first check the schema with the same name as user and then the public schema. You can override this behavior at the user level or the database level. For example, if we wanted all objects in contrib to be accessible without schema qualification, we would change our database as follows:

ALTER DATABASE mydb SET search_path="$user",public,contrib;

Schemas are also used for simple abstraction. A table name only needs to be unique within the schema, so many applications exploit this by creating same named tables in different schemas and, depending on who is logging in, they will get their own version based on which is their primary schema.

Permissions are one of the trickiest things to get right in PostgreSQL. This is one feature that we find more difficult to work with than other databases. Permission management became a lot easier with the advent of PostgreSQL 9.0+. PostgreSQL 9.0 introduced default permissions, which allowed for setting permissions on all objects of a particular schema or database as well as permissions on specific types of objects. More details on permissions management are detailed in the manual, in sections ALTER DEFAULT PRIVILEGES and GRANT.

Getting back to our contrib schema. Let’s suppose we want all users of our database to have EXECUTE and SELECT access to any tables and functions we will create in the contrib schema. We can define permissions as shown in Example 2-5:

GRANT USAGE ON SCHEMA contrib TO public;
ALTER DEFAULT PRIVILEGES IN SCHEMA contrib 
GRANT SELECT, REFERENCES ON TABLES
    TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA contrib
    GRANT SELECT, UPDATE ON SEQUENCES
    TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA contrib
    GRANT EXECUTE ON FUNCTIONS
    TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA contrib
    GRANT USAGE ON TYPES
    TO public;

If you already have your schema set with all the tables and functions, you can retroactively set permissions on each object separately or do this for all existing tables, functions, and sequences with a GRANT .. ALL .. IN SCHEMA.

GRANT USAGE ON SCHEMA contrib TO public;
GRANT SELECT, REFERENCES, TRIGGER 
	ON ALL TABLES IN SCHEMA contrib 
	TO public;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA contrib TO public;
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA contrib TO public;

Regardless of how you install an extension in your database, you’ll need to have gathered all the dependent libraries in your PostgreSQL bin and lib, or have them accessible via your system path. For small extensions, most of these libraries already come prepackaged with your PostgreSQL install so you don’t have to worry. For others, you’ll either need to compile your own, get them with a separate install, or copy the files from another equivalent setup.

psql -p 5432 -d postgres -f /usr/pgsql-9.0/share/contrib/adminpack.sql

CREATE EXTENSION fuzzystrmatch;

CREATE EXTENSION fuzzystrmatch SCHEMA my_extensions;

CREATE EXTENSION tablefunc SCHEMA contrib FROM unpackaged;

Many extensions come packaged with PostgreSQL, but are not installed by default. Some past extensions have gained enough traction to become part of the PostgreSQL core, so if you’re upgrading from an ancient version, you may not even have to worry about extensions.

For day-to-day backup, pg_dump is generally more expeditious than pg_dumpall because it can selectively backup tables, schemas, databases. pg_dump backs up to plain SQL, but also compressed and TAR formats. Compressed and TAR backups can take advantage of the parallel restore feature introduced in 8.4. Refer to Database Backup: pg_dump for a listing of pg_dump command options.

In this example, we’ll show a few common backup scenarios and corresponding pg_dump switches. These examples should work for any version of PostgreSQL.

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.payments* -f payment_tables.backup mydb

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr_payroll_schemas.backup mydb

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_schema_except_public.backup mydb

pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts -f select_tables.backup mydb

The Directory format option was introduced in PostgreSQL 9.1. This option backs up each table as a separate file in a folder and gets around the problem where your file system has limitations on the size of each file. It is the only pg_dump backup format option that generates multiple files. An example of this is shown in Example 2-8. The directory backup first creates the directory to put the files in and errors out if the directory already exists.

pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb

The pg_dumpall utility is what you would use to backup all databases into a single plain-text file, along with server globals such as tablespace definitions and users. Refer to Server Backup: pg_dumpall for listing of available pg_dumpall command options.

It’s a good idea to backup globals such as roles and tablespace definitions on a daily basis. Although you can use pg_dumpall to backup databases as well, we generally don’t bother or do it—at most, once a month—since it would take much longer to restore the plain text backup for large databases.

To backup roles and tablespaces:

pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

If you only care about backing up roles and not tables spaces, you would use the roles only option:

pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only

Before you can perform a full drop and restore of a database or restore a particular table that’s in use, you’ll need to kill connections. Every once in a while, someone else (never you) will execute a query that he or she didn’t mean to and end up wasting resources. You could also run into a query that’s taking much longer than what you have the patience for. Should these things happen, you’ll either want to cancel the query on the connection or kill the connection entirely. To cancel running queries or to terminate connections, you elicit three administrative functions.

PostgreSQL, unlike some other databases, lets you embed functions that perform actions within a regular SELECT query. This means that though pg_terminate_backend() and pg_cancel_backend() can only act on one connection at a time, you can effectuate multiple connections by wrapping them in a SELECT. For example, let’s suppose a user (Regina) was hogging up resources and had 100 connections going. We can kill all her connections by running this command:

Before 9.2:

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE usename = 'regina';

9.2 and after:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'regina';

pg_stat_activity has changed considerably in PostgreSQL 9.2 with renaming and addition of new columns. For example, procpid is now pid. More details about the changes and enhancements are detailed in PostgreSQL 9.2 Monitoring Enhancements.

A plain SQL backup is nothing more than a text file of a huge SQL script. It’s the least convenient of backups to have, but it’s portable across different database systems. With SQL backup, you must execute the entire script, there’s no partial restore, unless you’re willing to manually edit the file. Since there are no options, the backups are simple to restore by using the -f psql switch as shown in Example 2-10. However, they are useful if you need to load data to another DBMS with some editing.

psql -U postgres -f myglobals.sql

psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql

psql -U postgres -d mydb -f select_objects.sql

If you backed up using pg_dump, and specified a non-plain text backup format like tar, custom, or directory, you can use the versatile pg_restore utility for the restore. pg_restore provides you with a dizzying array of options for restoration and far surpasses any restoration utility found in other database systems. Here are some of its outstanding features:

Refer to Database Backup: pg_restore for a listing of pg_restore command options.

A basic restore command of a compressed or TAR backup would be to first create the database in SQL:

CREATE DATABASE mydb;

and then restore:

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

If the database is the same as the one you backed up, you can create the database in ones step with the following:

pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup

If you are running 9.2, you can take advantage of the --section switch to restore just the table structure without the actual data. This is useful if you want to use an existing database as a template for a new one. To do so, we would first create the target database using psql or pgAdmin:

CREATE DATABASE mydb2;

and then use pg_restore:

pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

To create a tablespace, you just need to denote a logical name and a physical folder. The postgres service account needs to have full access to this folder. If you are on a Windows server, use the following command (note the use of Unix-style slashes):

CREATE TABLESPACE secondary LOCATION 'C:/pgdata91_secondary';

For Unix-based systems, you first have to create the folder or define an fstab location then use this command:

CREATE TABLESPACE secondary LOCATION '/usr/data/pgdata91_secondary';

You can shuffle database objects among different tablespaces. To move all objects in the database to our secondary tablespace:

ALTER DATABASE mydb SET TABLESPACE secondary;

To move just a table:

ALTER TABLE mytable SET TABLESPACE secondary;

When people run out of disk space, the first thing they do is panic and start deleting files from the PostgreSQL data cluster folder because it’s so big. Part of the reason why this mistake happens so frequently is that some folders such as pg_log, pg_xlog, and pg_clog sound like logging folders that you expect to build up and be safe to delete. There are some files you can safely delete, and some that will destroy your data if you do.

The pg_log folder often found in your data folder is a folder that tends to build up, especially if you have logging enabled. Files in this folder can always be safely deleted without issues. In fact, many people just schedule jobs to delete them.

Files in the other folders except for pg_xlog should never be deleted, even if they sound like logs. In particular, don’t even think of touching pg_clog, the active commit log, without getting into trouble.

pg_xlog stores transaction logs. Some systems we’ve seen are configured to move processed transaction logs in a subfolder called archive. You’ll often have an archive folder somewhere (not necessarily as a subfolder of pg_xlog) if you are running synchoronous replication, continuous archiving, or just keeping around logs if you need to revert to a different point in time. Deleting files in the root of pg_xlog will destroy data, however, deleting files in the archived folder will just prevent you from performing point-in-time recovery, or if a slave server hasn’t played back the logs, prevent them from fetching them. If you aren’t concerned about any of these scenarios, then it’s safe to delete or move files in the archive folder.

Be weary of overzealous anti-virus programs, especially on Windows. We’ve seen cases where AV software removed important binaries in the PostgreSQL bin folder. Should PostgreSQL fail to start on a Windows system, the event viewer is the first place to look for clues as to why.

Many people are under the misconception that the postgres account needs to have full administrative rights to the server. In fact, depending on your PostgreSQL version, if you give the postgres account full administrative rights to the server, your database server may not even start.

The postgres system account should always be created as a regular system user in the OS with just rights to the data cluster and additional tablespace folders. Most installers will set up the correct permissions for postgres. Don’t try to do postgres any favors by giving it more rights than it needs. Granting unnecessary rights leaves your system vulnerable should you fall under an SQL injection attack. There are cases where you’ll need to give the postgres account write/delete/read rights to folders or executables outside of the data cluster. With scheduled jobs that execute batch files, this need often arises. We advise you to practice restraint and only grant the minimum rights necessary to get the job done.

Loading up your server with RAM doesn’t mean you can set the shared_buffers as high as you’d like. Try it and your server may crash or refuse to start. If you are running PostgreSQL on 32-bit Windows, setting it higher than 512 MB often results in instability. With PostgreSQL 64-bit windows, you can push the envelop a bit higher and even exceed 1 GB without any issues. On some Linux systems, the compiled SHMMAX variable is low and shared_buffers can’t be set higher. Details on how to remedy this issue are detailed in the manual, in the section Kernel Resources.

If you do this, you’ll see errors in your pg_log files of the form. Make sure PostgreSQL is not already running. Here are the common reasons why this happens: