Making Configurations Take Effect

Some configuration changes require a PostgreSQL service restart, which closes any active connections from clients. Other changes require just a reload. New users connecting after a reload will receive the new setting. Extant users with active connections will not be affected during a reload. If you’re not sure whether a configuration change requires a reload or restart, look under the context setting associated with a configuration. If the context is postmaster, you’ll need a restart. If the context is user, a reload will suffice.

pg_ctl reload -D your_data_directory_here

service postgresql-9.5 reload

SELECT pg_reload_conf();

service postgresql-9.6 restart

pg_ctl restart -D your_data_directory_here

The postgresql.conf File

postgresql.conf controls the life-sustaining settings of the PostgreSQL server. You can override many settings at the database, role, session, and even function levels. You’ll find many details on how to finetune your server by tweaking settings in the article Tuning Your PostgreSQL Server.

Version 9.4 introduced an important change: instead of editing postgresql.conf directly, you should override settings using an additional file called postgresql.auto.conf. We further recommend that you don’t touch the postgresql.conf and place any custom settings in postgresql.auto.conf.

SELECT
    name,
    context ,
    unit ,
    setting, boot_val, reset_val 
FROM pg_settings
WHERE name IN ('listen_addresses','deadlock_timeout','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 | *
shared_buffers       | postmaster | 8kB  | 131584  | 1024      | 131584
deadlock_timeout     | superuser  | ms   | 1000    | 1000      | 1000
effective_cache_size | user       | 8kB  | 16384   | 16384     | 16384
maintenance_work_mem | user       | kB   | 16384   | 16384     | 16384
work_mem             | user       | kB   | 5120    | 1024      | 5120

SELECT name, sourcefile, sourceline, setting, applied
FROM pg_file_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers',
    'effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY name;

name                 | sourcefile                    | sourceline | setting | applied
---------------------+-------------------------------+------------+---------+--------
effective_cache_size | E:/data96/postgresql.auto.conf|         11 | 8GB     | t
listen_addresses     | E:/data96/postgresql.conf     |         59 | *       | t
maintenance_work_mem | E:/data96/postgresql.auto.conf|          3 | 16MB    | t
shared_buffers       | E:/data96/postgresql.conf     |        115 | 128MB   | f
shared_buffers       | E:/data96/postgresql.auto.conf|          5 | 131584  | t

ALTER SYSTEM SET work_mem = '500MB';

SELECT pg_reload_conf();

include 'filename'

The pg_hba.conf File

The pg_hba.conf file controls which IP addresses and users can connect to the database. Furthermore, it dictates the authentication protocol that the client must follow. Changes to the file require at least a reload to take effect. A typical pg_hba.conf looks like Example 2-4.

# TYPE  DATABASE USER ADDRESS         METHOD
host    all      all  127.0.0.1/32    ident 
host    all      all  ::1/128         trust 
host    all      all  192.168.54.0/24 md5 
hostssl all      all  0.0.0.0/0       md5 

# TYPE DATABASE    USER     ADDRESS      METHOD
# Allow replication connections from localhost, 
# by a user with replication privilege. 
#host  replication postgres 127.0.0.1/32 trust
#host  replication postgres ::1/128      trust

For each connection request, pg_hba.conf is checked from the top down. As soon as a rule granting access is encountered, a connection is allowed and the server reads no further in the file. As soon as a rule rejecting access is encountered, the connection is denied and the server reads no further in the file. If the end of the file is reached without any matching rules, the connection is denied. A common mistake people make is to put the rules in the wrong order. For example, if you added 0.0.0.0/0 reject before 127.0.0.1/32 trust, local users won’t be able to connect, even though a rule is in place allowing them to.

New in version 10 is the pg_hba_file_rules system view that lists all the contents of the pg_hba.conf file.

Check for Queries Being Blocked

The pg_stat_activity view has changed considerably since version 9.1 with the renaming, dropping, and addition of new columns. Starting from version 9.2, procpid was renamed to pid.

pg_stat_activity changed in PostgreSQL 9.6 to provide more detail about waiting queries. In prior versions of PostgreSQL, there was a field called waiting that could take the value true or false. true denoted a query that was being blocked waiting some resource, but the resource being waited for was never stated. In PostgreSQL 9.6, waiting was removed and replaced with wait_event_type and wait_event to provide more information about what resource a query was waiting for. Therefore, prior to PostgreSQL 9.6, use waiting = true to determine what queries are being blocked. In PostgreSQL 9.6 or higher, use wait_event IS NOT NULL.

In addition to the change in structure, PostgreSQL 9.6 will now track additional wait locks that did not get set to waiting=true in prior versions. As a result, you may find lighter lock waits being listed for queries than you saw in prior versions. For a list of different wait_event types, refer to PostgreSQL Manual: wait_event names and types.

Creating Login Roles

When you initialize the data cluster during setup, PostgreSQL creates a single login role with the name postgres. (PostgreSQL also creates a namesake database called postgres.) You can bypass the password setting by mapping an OS root user to the new role and using ident, peer, or trust for authentication. After you’ve installed PostgreSQL, before you do anything else, you should log in as postgres and create other roles. pgAdmin has a graphical section for creating user roles, but if you want to create one using SQL, execute an SQL command like the one shown in Example 2-5.

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

Specifying VALID UNTIL is optional. If omitted, the role remains active indefinitely. CREATEDB grants database creation privilege to the new role.

To create a user with superuser privileges, follow Example 2-6. Naturally, you must be a superuser to create other superusers.

CREATE ROLE regina LOGIN PASSWORD 'queen' VALID UNTIL '2020-1-1 00:00' SUPERUSER;

Both of the previous examples create roles that can log in. To create roles that cannot log in, omit the LOGIN PASSWORD clause.

Creating Group Roles

Group roles generally cannot log in. Rather, they serve as containers for other roles. This is merely a best-practice suggestion. Nothing stops you from creating a role that can log in as well as contain other roles.

Create a group role using the following SQL:

CREATE ROLE royalty INHERIT;

Note the use of the modifier INHERIT. This means that any member of royalty will automatically inherit privileges of the royalty role, except for the superuser privilege. For security, PostgreSQL never passes down the superuser privilege. INHERIT is the default, but we recommend that you always include the modifier for clarity.

To refrain from passing privileges from the group to its members, create the role with the NOINHERIT modifier.

To add members to a group role, you would do:

GRANT royalty TO leo;
GRANT royalty TO regina;

Some privileges can’t be inherited. For example, although you can create a group role that you mark as superuser, this doesn’t make its member roles superusers. However, those users can impersonate their group role by using the SET ROLE command, thereby gaining superuser privileges for the duration of the session. For example:

Let’s give the royalty role superuser rights with the command:

ALTER ROLE royalty SUPERUSER;

Although leo is a member of the royalty group and he inherits most rights of royalty, when he logs in, he still will not have superuser rights. He can gain superuser rights by doing:

SET ROLE royalty;

His superuser rights will last only for his current session.

This feature, though peculiar, is useful if you want to prevent yourself from unintentionally doing superuser things while you are logged in.

SET ROLE is a command available to all users, but a more powerful command called SET SESSION AUTHORIZATION is available to people who log in as superusers. In order to understand the differences, we’ll first introduce two global variables that PostgreSQL has called: current_user and session_user. You can see these values when you log in by running the SQL statement:

SELECT session_user, current_user;

When you first log in, the values of these two variables are the same. SET ROLE changes the current_user, while SET SESSION AUTHORIZATION changes both the current_user and session_user variables.

Here are the salient properties of SET ROLE:

• SET ROLE does not require superuser rights.

• SET ROLE changes the current_user variable, but not the session_user variable.

• A session_user that has superuser rights can SET ROLE to any other role.

• Nonsuperusers can SET ROLE only to the role the session_user is or the roles the session_user belongs to.

• When you do SET ROLE you gain all privileges of the impersonated user except for SET SESSION_AUTHORIZATION and SET ROLE.

A more powerful command, SET SESSION AUTHORIZATION, is available as well. Key features of SET SESSION AUTHORIZATION are as follows:

• Only a user that logs in as a superuser has permission to do SET SESSION AUTHORIZATION to another role.

• The SET SESSION AUTHORIZATION privilege is in effect for the life of the session, meaning that even if you SET SESSION AUTHORIZATION to a user that is not a superuser, you still have the SET SESSION AUTHORIZATION privilege for the life of your session.

• SET SESSION AUTHORIZATION changes the values of the current_user and session_user variables to those of the user being impersonated.

• A session_user that has superuser rights can SET ROLE to any other role.

We’ll do a set of exercises that illustrate the differences between SET ROLE and SET SESSION AUTHORIZATION by first logging in as leo and then running the code in Example 2-7.

SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 leo          | leo
(1 row)

SET SESSION AUTHORIZATION regina;

ERROR:  permission denied to set session authorization

SET ROLE regina;

ERROR:  permission denied to set role "regina"

ALTER ROLE leo SUPERUSER;

ERROR:  must be superuser to alter superusers

SET ROLE royalty;
SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 leo          | royalty
(1 row)

SET ROLE regina;

ERROR:  permission denied to set role "regina"

ALTER ROLE leo SUPERUSER;
SET ROLE regina;
SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 leo          | regina
(1 row)

SET SESSION AUTHORIZATION regina;

ERROR:  permission denied to set session authorization

-- After ending session and logging back in as leo
SELECT session_user, current_user;
SET SESSION AUTHORIZATION regina;
SELECT session_user, current_user;

session_user | current_user
--------------+--------------
leo | leo
(1 row)
SET SESSION AUTHORIZATION
session_user | current_user
--------------+--------------
regina | regina
(1 row)

In Example 2-7 leo was unable to use SET SESSION AUTHORIZATION because he’s not a superuser. He was also unable to SET ROLE to regina because he is not in the regina group. However, he was able to SET ROLE royalty since he is a member of the royalty group (he’s a king consort). Even though royalty has superuser rights, he still wasn’t able to impersonate the queen, regina, because his SET ROLE abilities are still based on being the powerless leo. Since royalty is a group that has superuser rights, he was able to promote his own account leo to be a superuser. Once leo is promoted to power, he can then impersonate regina. He is now able to completely take over her session_user and current_user persona with SET SESSION AUTHORIZATION.

Template Databases

A template database is, as the name suggests, a database that serves as a skeleton for new databases. When you create a new database, PostgreSQL copies all the database settings and data from the template database to the new database.

The default PostgreSQL installation comes with two template databases: template0 and template1. If you don’t specify a template database to follow when you create a database, template1 is used.

The basic syntax to create a database modeled after a specific template is:

CREATE DATABASE my_db TEMPLATE my_template_db;

You can pick any database to serve as the template. This could come in quite handy when making replicas. You can also mark any database as a template database. Once you do, the database is no longer editable and deletable. Any role with the CREATEDB privilege can use a template database. To make any database a template, run the following SQL as a superuser:

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

If ever you need to edit or drop a template database, first set the datistemplate attribute to FALSE. Don’t forget to change the value back after you’re done with edits.

Using Schemas

Schemas organize your database into logical groups. If you have more than two dozen tables in your database, consider cubbyholing them into schemas. Objects must have unique names within a schema but need not be unique across the database. If you cram all your tables into the default public schema, you’ll run into name clashes sooner or later. It’s up to you how to organize your schemas. For example, if you are an airline, you can place all tables of planes you own and their maintenance records into a planes schema. Place all your crew and staff into an employees schema and place all passenger-related information into a passengers schema.

Another common way to organize schemas is by roles. We found this to be particularly handy with applications that serve multiple clients whose data must be kept separate.

Suppose that you started a dog beauty management business (doggie spa). You start with a table in public called dogs to track all the dogs you hope to groom. You convince your two best friends to become customers. Whimsical government privacy regulation passes, and now you have to put in iron-clad assurances that one customer cannot see dog information from another. To comply, you set up one schema per customer and create the same dogs table in each as follows:

CREATE SCHEMA customer1;
CREATE SCHEMA customer2;

You then move the dog records into the schema that corresponds with the client. The final touch is to create different login roles for each schema with the same name as the schema. Dogs are now completely isolated in their respective schemas. When customers log in to your database to make appointments, they will be able to access only information pertaining to their own dogs.

Wait, it gets better. Because we named our roles to match their respective schemas, we’re blessed with another useful technique. But we must first introduce the search_path database variable.

As we mentioned earlier, object names must be unique within a schema, but you can have same-named objects in different schemas. For example, you have the same table called dogs in all 12 of your schemas. When you execute something like SELECT * FROM dogs, how does PostgreSQL know which schema you’re referring to? The simple answer is to always prepend the schema name onto the table name with a dot, such as in SELECT * FROM customer1.dogs. Another method is to set the search_path variable to be something like customer1, public. When the query executes, the planner searches for the dogs table first in the customer1 schema. If not found, it continues to the public schema and stops there.

PostgreSQL has a little-known variable called user that retrieves the role currently logged in. SELECT user returns this name. user is just an alias for current_user, so you can use either.

Recall how we named our customers’ schemas to be the same as their login roles. We did this so that we can take advantage of the default search path set in postgresql.conf:

search_path = "$user", public;

Now, if role customer1 logs in, all queries will first look in the customer1 schema for the tables before moving to public. Most importantly, the SQL remains the same for all customers. Even if the business grows to have thousands or hundreds of thousands of dog owners, none of the SQL scripts need to change. Commonly shared tables such as common lookup tables can be put in the public schema.

Another practice that we strongly encourage is to create schemas to house extensions (“Step 2: Installing into a database”). When you install an extension, new tables, functions, data types, and plenty of other relics join your server. If they all swarm into the public schema, it gets cluttered. For example, the entire PostGIS suite of extensions will together add thousands of functions. If you’ve already created a few tables and functions of your own in the public schema, imagine how maddening it would be to scan a list of tables and functions trying to find your own among the thousands.

Before you install any extensions, create a new schema:

CREATE SCHEMA my_extensions;

Then add your new schema to the search path:

ALTER DATABASE mydb SET search_path='$user', public, my_extensions;

When you install extensions, be sure to indicate your new schema as their new home.

Types of Privileges

PostgreSQL has a few dozen privileges, some of which you may never need to worry about. The more mundane privileges are SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, and TRUNCATE.

Most privileges must have a context. For example, a role having an ALTER privilege is meaningless unless qualified with a database object such as ALTER privilege on tables1, SELECT privilege on table2, EXECUTE privilege on function1, and so on. Not all privileges apply to all objects: an EXECUTE privilege for a table is nonsense.

Some privileges make sense without a context. CREATEDB and CREATE ROLE are two privileges where context is irrelevant.

Getting Started

So you successfully installed PostgreSQL; you should have one superuser, whose password you know by heart. Now you should take the following steps to set up additional roles and assign privileges:

1. PostgreSQL creates one superuser and one database for you at installation, both named postgres. Log in to your server as postgres.

2. Before creating your first database, create a role that will own the database and can log in, such as:

   CREATE ROLE mydb_admin LOGIN PASSWORD 'something';

3. Create the database and set the owner:

   CREATE DATABASE mydb WITH owner = mydb_admin;

4. Now log in as the mydb_admin user and start setting up additional schemas and tables.

GRANT

The GRANT command is the primary means to assign privileges. Basic usage is:

GRANT some_privilege TO some_role;

A few things to keep in mind when it comes to GRANT:

• Obviously, you need to have the privilege you’re granting. And, you must have the GRANT privilege yourself. You can’t give away what you don’t have.

• Some privileges always remain with the owner of an object and can never be granted away. These include DROP and ALTER.

• The owner of an object retains all privileges. Granting an owner privilege in what it already owns is unnecessary. Keep in mind, though, that ownership does not drill down to child objects. For instance, if you own a database, you may not necessarily own all the schemas within it.

• When granting privileges, you can add WITH GRANT OPTION. This means that the grantee can grant her own privileges to others, passing them on:

  GRANT ALL ON ALL TABLES IN SCHEMA public TO mydb_admin WITH GRANT OPTION;

• To grant specific privileges on ALL objects of a specific type use ALL instead of the specific object name, as in:

  GRANT SELECT, REFERENCES, TRIGGER ON 
  ALL TABLES IN SCHEMA my_schema TO 
  PUBLIC;

  Note that ALL TABLES includes regular tables, foreign tables, and views.

• To grant privileges to all roles, you can use the PUBLIC alias, as in:

  GRANT USAGE ON SCHEMA my_schema TO PUBLIC;

The GRANT command is covered in detail in GRANT. We strongly recommend that you take the time to study this document before you inadvertently knock a big hole in your security wall.

Some privileges are, by default, granted to PUBLIC. These are CONNECT and CREATE TEMP TABLE for databases and EXECUTE for functions. In many cases you might consider revoking some of the defaults with the REVOKE command, as in:

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema FROM PUBLIC;

Default Privileges

Default privileges ease privilege management by letting you set privileges before their creation.

Let’s suppose we want all users of our database to have EXECUTE and SELECT privileges access to any future tables and functions in a particular schema. We can define privileges as shown in Example 2-8. All roles of a PostgreSQL server are members of the group PUBLIC.

GRANT USAGE ON SCHEMA my_schema TO PUBLIC; 

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT SELECT, REFERENCES ON TABLES TO PUBLIC; 

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT ALL ON TABLES TO mydb_admin WITH GRANT OPTION;  

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

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema 
GRANT ALL ON FUNCTIONS TO mydb_admin WITH GRANT OPTION;

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema 
GRANT USAGE ON TYPES TO PUBLIC;

Allows all users that can connect to the database to also be able to use and create objects in a schema if they have rights to those objects in the schema. GRANT USAGE on a schema is the first step to granting access to objects in the schema. If a user has rights to select from a table in a schema but no USAGE on the schema, then he will not be able to query the table.

Grant read and reference rights (the ability to create foreign key constraints against columns in a table) for all future tables created in a schema to all users that have USAGE of the schema.

GRANT ALL permissions on future tables to role mydb_admin. In addition, allow members in mydb_admin to be able to grant a subset or all privileges to other users to future tables in this schema. GRANT ALL gives permission to add/update/delete/truncate rows, add triggers, and create constraints on the tables.

GRANT permissions on future sequences, functions, and types.

To read more about default privileges, see ALTER DEFAULT PRIVILEGES.

Privilege Idiosyncrasies

Before we unleash you to explore privileges on your own, we do want to point out a few quirks that may not be apparent.

Unlike in other database products, being the owner of a PostgreSQL database does not give you access to all objects in the database. Another role could conceivably create a table in your database and deny you access to it! However, the privilege to drop the entire database could never be wrestled away from you.

After granting privileges to tables and functions with a schema, don’t forget to grant usage on the schema itself.

Installing Extensions

Getting an extension into your database takes two installation steps. First, download the extension and install it onto your server. Second, install the extension into your database.

We cover both steps in this section as well as how to install on PostgreSQL versions prior to extension support.

SELECT * FROM pg_available_extensions;

CREATE EXTENSION fuzzystrmatch;

psql -p 5432 -d mydb -c "CREATE EXTENSION fuzzystrmatch;"

CREATE EXTENSION fuzzystrmatch SCHEMA my_extensions;

CREATE EXTENSION tablefunc SCHEMA contrib FROM unpackaged;

Common Extensions

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. If you’re upgrading from an ancient version, you may gain functionality without needing any extensions.

Selective Backup Using pg_dump

For day-to-day backup, pg_dump is more expeditious than pg_dumpall because pg_dump can selectively back up tables, schemas, and databases. pg_dump can back up to plain SQL, as well as compressed, TAR, and directory formats. Compressed, TAR, and directory format backups can take advantage of the parallel restore feature of pg_restore. Directory backups allow parallel pg_dump of a large database. Because we believe you’ll be using pg_dump as part of your daily regimen, we have included a full dump of the help in “Database Backup Using pg_dump” so you can see the myriad switches in a single glance.

The next examples demonstrate a few common backup scenarios and corresponding pg_dump options. They should work for any version of PostgreSQL.

To create a compressed, single database backup:

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

To create a plain-text single database backup, including a -C option, which stands for CREATE DATABASE:

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

To create a compressed backup of tables whose names start with pay in any schema:

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

To create a compressed backup of all objects in the hr and payroll schemas:

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

To create a compressed backup of all objects in all schemas, excluding the public schema:

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

To create a plain-text SQL backup of select tables, useful for porting structure and data to lower versions of PostgreSQL or non-PostgreSQL databases (plain text generates an SQL script that you can run on any system that speaks SQL):

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

The directory format option was introduced in version PostgreSQL 9.1. This option backs up each table as a separate file in a folder and gets around file size limitations. This option is the only pg_dump backup format option that results in multiple files, as shown in Example 2-10. It creates a new directory and populates it with a gzipped file for each table; also included is a file listing the hierarchy. This backup command exits with an error if the directory already exists.

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

A parallel backup option was introduced in version 9.3 using the --jobs or -j option and specifying the number of jobs. For example: --jobs=3 (-j 3) runs three backups in parallel. Parallel backup makes sense only with the directory format option, because it’s the only backup where multiple files are created. Example 2-11 demonstrates its use.

pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb

Systemwide Backup Using pg_dumpall

Use the pg_dumpall utility to back up all databases on a server into a single plain-text file. This comprehensive backup automatically includes server globals such as tablespace definitions and roles. See “Server Backup: pg_dumpall” for a listing of available pg_dumpall command options.

It’s a good idea to back up globals on a daily basis. Although you can use pg_dumpall to back up databases as well, we prefer backing up databases individually using pg_dump or using pg_basebackup to do a PostgreSQL service-level backup. Restoring from a huge plain-text backup tries our patience. Using pg_basebackup in conjunction with streaming replication is the fastest way to recover from major server failure.

To back up all globals and tablespace definitions only, use the following:

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

To back up specific global settings, use the following:

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

Restoring Data

There are two ways to restore data in PostgreSQL from backups created with pg_dump or pg_dumpall:

• Use psql to restore plain-text backups generated with pg_dumpall or pg_dump.

• Use pg_restore to restore compressed, TAR, and directory backups created with pg_dump.

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

CREATE DATABASE mydb;

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

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

CREATE DATABASE mydb2;

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

Creating Tablespaces

To create a new tablespace, specify a logical name and a physical folder and make sure that the postgres service account has full access to the physical folder. If you are on a Windows server, use the following command (note the use of Unix-style forwardslashes):

CREATE TABLESPACE secondary LOCATION 'C:/pgdata94_secondary';

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

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

Moving Objects Among Tablespaces

You can shuffle database objects among different tablespaces. To move all objects in the database to your secondary tablespace, issue the following SQL command:

ALTER DATABASE mydb SET TABLESPACE secondary;

To move just one table:

ALTER TABLE mytable SET TABLESPACE secondary;

New in PostgreSQL 9.4 is the ability move a group of objects from one tablespace to another. If the role running the command is a superuser, all objects will be moved. If not, only the owned objects will be moved.

To move all objects from default tablespace to secondary use:

ALTER TABLESPACE pg_default MOVE ALL TO secondary;

During the move, your database or table will be locked.

Don’t Delete PostgreSQL Core System Files and Binaries

Perhaps this is stating the obvious, but when people run out of disk space, the first thing they do is start deleting files from the PostgreSQL data cluster folder because it’s so darn big. Part of the reason this mistake happens so frequently is that some folders sport innocuous names such as pg_log, pg_xlog, and pg_clog. Yes, there are some files you can safely delete, but unless you know precisely which ones, you could end up destroying your data.

The pg_log folder, often found in your data folder, is a folder that builds up quickly, especially if you have logging enabled. You can always purge files from this folder without harm. In fact, many people schedule jobs to remove logfiles on a regular basis.

Files in the other folders, except for pg_xlog, should never be deleted, even if they have log-sounding names. Don’t even think of touching pg_clog, the active commit log, unless you want to invite disaster.

pg_xlog stores transaction logs. Some systems we’ve seen are configured to move processed transaction logs into a subfolder called archive. You’ll often have an archive folder somewhere (not necessarily as a subfolder of pg_xlog) if you are running synchronous replication, doing continuous archiving, or just keeping logs around in case you need to revert to a different point in time. Deleting files in the root of pg_xlog will mess up the process. 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, will prevent the slave from fetching them. If these scenarios don’t apply to you, it’s safe to remove files in the archive folder.

Be leery of overzealous antivirus programs, especially on Windows. We’ve seen cases in which antivirus software removed important binaries in the PostgreSQL bin folder. If PostgreSQL fails to start on a Windows system, the event viewer is the first place to look for clues as to why.

Don’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)

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

The postgres account should always be created as a regular system user in the OS with privileges just to the data cluster and additional tablespace folders. Most installers will set up the correct permissions without you needing to worry. Don’t try to do postgres any favors by giving it more access than it needs. Granting unnecessary access leaves your system vulnerable if you fall victim to 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 and FDWs that have foreign tables in files, this need often arises. Practice restraint and bestow only the minimum access necessary to get the job done.

Don’t Set shared_buffers Too High

Loading up your server with RAM doesn’t mean you can set the shared_buffers as high as your physical RAM. 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 64-bit Windows, you can push the envelope higher, and can even exceed 8 GB without any issues. On some Linux systems, shared_buffers can’t be higher than the SHMMAX variable, which is usually quite low.

PostgreSQL 9.3 changed how kernel memory is used, so that many of the issues people ran into with limitations in prior versions are no longer issues. You can find more details in Kernel Resources.

Don’t Try to Start PostgreSQL on a Port Already in Use

If you try to start PostgreSQL on a port that’s already in use, 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:

• You’ve already started the postgres service.

• You are trying to run PostgreSQL on a port already in use by another service.

• Your postgres service had a sudden shutdown and you have an orphan postgresql.pid file in the data folder. Delete the file and try again.

• You have an orphaned PostgreSQL process. When all else fails, kill all running PostgreSQL processes and then try starting again.