Knowledge Base
Backup/Recovery
Q How do I implement real-time WAL backups?
A In postgresql.conf, enable WAL archiving by setting wal_level to archive or hot_standby.
Q The host name defined for a remote backup cannot be found.
A Open /etc/host and ensure that it contains the host name of the remote backup and its respective IP address.
Q How much storage space will I need to execute pg_basebackup on the same server?
A You will need at least twice the amount of space used by the instance.
Q The pg_basebackup command fails from a cascaded standby.
A This is a known issue in cascade setups running PostgreSQL versions lower than 9.5.6.
If your version is lower than 9.5.6 (the current version can be obtained using select version();), please patch it up to at least version 9.5.6.
Configuration
Q I changed a parameter in postgresql.conf, but it did not take effect as I expected.
A Not all parameters are immediately reflected to database clusters — some require executing pg_ctl with either reload or restart.
Some parameter changes may take effect with just reload, while others require restart.
Q How do I change a configuration parameter value without having to restart the database cluster?
A Use ALTER SYSTEM SET paramName=paramValue to change a configuration parameter on the fly.
Please note, though, that not all parameters can be changed in this manner.
Q I experience intermittent connection errors when repeatedly connecting to PostgreSQL using SSL.
A When encountered intermittently, this typically means that there are no available sockets because too many are in wait state — TIME_WAIT or, less probably, FIN_WAIT_1 or FIN_WAIT_2.
Q I can't access my streaming standby database, even in read mode?
A On the standby, open postgresql.conf and set hot_standby to on.
Note that if the standby is in sync streaming replication mode, then the master will not complete requests until the standby database (not the server itself) is restarted — just reloading the configuration is not enough.
Operation
Q How can I perform a query that involves more than 1 database?
A You must use the foreign data wrapper postgres_fdw, which is built-in to PostgreSQL 9.3 and includes read/write support. For further details, refer to "postgres_fdw" in the PostgreSQL online documentation.
Q Are PostgreSQL column names case-sensitive?
A Yes. All identifiers, including column names, are converted to lowercase in PostgreSQL, unless enclosed by double quotes. Identifiers created with double-quotes retain their original capitalization.
High Availability
Q How do I drop replication slots?
A Though replication slots are very useful in cases where the standby server needs to be kept up-to-date even after being disconnected from the master server for a long period, there are cases when they might not be the right fit.
To drop a replication slot follow the steps below:
Stop the standby server:
1. Log in to the primary node.
2. Drop the replication slot using select pg_drop_replication_slot('slotName');
3. Confirm that the replication slot was dropped using select * from pg_replication_slots;
4. In the standby server, open recovery.conf and comment the entry primary_slot_name='slotName'
5. Start the standby server using pg_ctl start -D $PGDATA
Performance
Q It takes a long time to create the indexes. How can I speed this up?
A Indexes are created using memory set aside by the maintenance_work_mem parameter, so this value needs to be big enough for the table. If index creation is taking too long in your environment, then you need to increase its value, to around 3 times as much as required during tests. Note that maintenance_work_mem is specified in units of 8k blocks, so a value of 1 means 8k. Tweak the value until the server stops using any temporary files.
Administration
Q How do I find out what tables, indexes, databases, and user are defined?
A In psql, use the options \dt, \di , \l, and \du, respectively. This information can also be obtained from the system views pg_tables, pg_indexes, pg_database, and pg_user, respectively.
Q How do I check if a table exists in a given schema?
A Use the query below:
Q How do I select the nth row?
A Use the OFFSET key word, as follows:
For example, using LIMIT 1 OFFSET 19 will return the 20th row, and using LIMIT 10 OFFSET 19 will return rows 20–29.
Q The pg_rewind command failed the first time I tried to run it, but it worked correct in subsequent attempts.
A This is a known issue in cascade setups running PostgreSQL versions lower than 9.5.6.
If your version is lower than 9.5.6 (the current version can be obtained using select version();), please patch it up to at least version 9.5.6.
Q Why does PostgreSQL have so many processes, even when idle?
A PostgreSQL is process-based, and starts one postgres instance per connection, plus one or more "helper" processes, such as the stats collector, background writer, autovacuum daemon, walsender, etc.
Q Why do I receive the message sorry, too many clients already when trying to connect?
A This message is returned when the limit of database sessions is reached — the default value is 100. Either raise the connection limit by setting max_connection and shared_buffers in postgresql.conf or add a connection pooler.
General
Q How should I handle reserved key words in PostgreSQL?
A SQL distinguishes between reserved and non-reserved key words. According to the standard, reserved key words are the only real key words — they are never allowed as identifiers. Non-reserved key words only have a special meaning in particular contexts, and can be used as identifiers in other contexts.
As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier, you should try to quote the identifier to see if the problem goes away.
Error: In the example below, PRIMARY is a reserved key word.
ERROR: syntax error (10474) at or near "NUMERIC" (10620)
LINE 1: CREATE TABLE TEST ( PRIMARY NUMERIC);
^
Solution: Quote the identifier so that it can be used.
CREATE TABLE
postgres=# \d TEST
Table "public.text"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
PRIMARY | numeric | | |
