PostgreSQL

Become the postgres user. Start the primary database shell, psql, where you can do all your creation of databases/tables, deletion, set permissions, and run raw SQL commands. Use the -d option to connect to the database you created (without specifying a database, will try to access a database that matches your username).

[postgres]$ psql -d myDatabaseName

Some helpful commands:

Get help:

=> \help

Connect to a particular database:

=> \c <database>

List all users and their permission levels:

=> \du

Show summary information about all tables in the current database:

=> \dt

Exit/quit the shell:

=> \q or CTRL+d

There are of course many more meta-commands, but these should help you get started. To see all meta-commands run:

=> \?

The defaults allow any local user to connect as any database user, including the database superuser. This is likely not what you want, so in order to restrict global access to the postgres user, change the following line:

To:

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                peer

You might later add additional lines depending on your needs or software ones.

Edit and set the authentication method for each user (or "all" to affect all users) to (preferred), or (less secure; should be avoided if possible):

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD                                                                                               
                                                                                                                                                                                                                                                              
# "local" is for Unix domain socket connections only                                                                                                                        
local   all             ''user''                                    scram-sha-256

If you choose , you must also edit and set:

Restart postgresql.service, and then re-add each user's password using .

In the connections and authentications section of your configuration, set:

This will disable network listening completely. After this you should restart postgresql.service for the changes to take effect.

In the connections and authentications section, set the line to your needs:

You can use to listen on all available addresses.

Then add a line like the following to the authentication config:

where is the IP address of the remote client.

See the documentation for pg_hba.conf.

After this you should restart postgresql.service for the changes to take effect.

For troubleshooting take a look in the server log file:

# journalctl -u postgresql.service

PostgreSQL offers a number of authentication methods. If you would like to allow users to authenticate with their system password, additional steps are necessary. First you need to enable PAM for the connection.

For example, the same configuration as above, but with PAM enabled:

The PostgreSQL server is however running without root privileges and will not be able to access . We can work around that by allowing the postgres group to access this file:

# setfacl -m g:postgres:r /etc/shadow

The default directory where all your newly created databases will be stored is . To change this, follow these steps:

Create the new directory and make the postgres user its owner:

# mkdir -p /pathto/pgroot/data
# chown -R postgres:postgres /pathto/pgroot

Become the postgres user, and initialize the new cluster:

[postgres]$ initdb -D /pathto/pgroot/data

Edit postgresql.service to create a drop-in file and override the Environment and settings. For example:

If you want to use directory for default directory or for tablespaces, add one more line in this file:

ProtectHome=false

When creating a new database (e.g. with createdb blog) PostgreSQL actually copies a template database. There are two predefined templates: is vanilla, while is meant as an on-site template changeable by the administrator and is used by default. In order to change the encoding of a new database, one of the options is to change on-site . To do this, log into PostgreSQL shell () and execute the following:

First, we need to drop . Templates cannot be dropped, so we first modify it so it is an ordinary database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Now we can drop it:

DROP DATABASE template1;

The next step is to create a new database from , with a new default encoding:

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

Now modify so it is actually a template:

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

Optionally, if you do not want anyone connecting to this template, set to :

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Now you can create a new database:

[postgres]$ createdb blog

If you log back in to and check the databases, you should see the proper encoding of your new database:

For those wishing to use pg_upgrade, a package is available that will always run one major version behind the real PostgreSQL package. This can be installed side-by-side with the new version of PostgreSQL. To upgrade from older versions of PostgreSQL there are AUR packages available: , , , , postgresql-92-upgrade^AUR. Read the man page to understand what actions it performs.

Note that the databases cluster directory does not change from version to version, so before running pg_upgrade, it is necessary to rename your existing data directory and migrate into a new directory. The new databases cluster must be initialized, as described in the #Installation section.

While the database is still accessible, one may take the opportunity to check the locale and encoding used, and whether data checksums are used:

When you are ready :

1. Stop postgresql.service:
   1. Check the unit status to be sure that PostgresSQL was stopped correctly. If it failed, pg_upgrade will fail too.
2. Upgrade postgresql, , and .
3. Finally upgrade the databases cluster:
   1. Rename the databases cluster directory, and create an empty one:
   2. Should you have a conflicting system locale and encoding, add --locale=xy_XY.UTF-8 options (where xx_YY matches your need).
   3. Should you have data-checksums enabled, add option.
   4. Upgrade the cluster, replacing PG_VERSION below, with the old PostgreSQL version number (e.g. ):
   5. Start postgresql.service again.

pg_upgrade will have created the scripts and in and will have output some instructions about running these.

• generates optimizer statistics for the new cluster and should be run as user postgres. It requires the service to have been started.
• simply deletes the directory and should be run as a user with write privileges for (e.g. as ).

You may delete the directory once the upgrade is completely over.

You could also do something like this (after the upgrade and install of ).

Stop postgresql.service

# mv /var/lib/postgres/data /var/lib/postgres/olddata
# mkdir /var/lib/postgres/data
# chown postgres:postgres /var/lib/postgres/data
[postgres]$ initdb -D /var/lib/postgres/data
[postgres]$ /opt/pgsql-12/bin/pg_ctl -D /var/lib/postgres/olddata/ start
# cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-12/lib/ # Only if postgis installed
[postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql
[postgres]$ /opt/pgsql-12/bin/pg_ctl -D /var/lib/postgres/olddata/ stop

Start postgresql.service

[postgres]$ psql -f /tmp/old_backup.sql postgres

If you are using PostgresSQL on a local machine for development and it seems slow, you could try turning synchronous_commit off in the configuration. Beware of the caveats, however.

PostgreSQL periodically updates its internal "statistics" file. By default, this file is stored on disk, which prevents disks from spinning down on laptops and causes hard drive seek noise. It is simple and safe to relocate this file to a memory-only file system with the following configuration option:

/var/lib/postgres/data/postgresql.conf

stats_temp_directory = '/run/postgresql'

If you see errors about when navigating the tree on the left, or about when viewing the data, remove the server from the connection list in pgAdmin and add a fresh server instance. pgAdmin will otherwise continue to treat the server as a PostgreSQL 11 server resulting in these issues.