This chapter covers what we deem to be the most common activities for basic administration of a PostgreSQL server: role and permission management, database creation, add-on installation, backup, and restore. We assume you’ve already installed PostgreSQL and have administration tools at your disposal.
The main configuration files that control basic operations of a PostgreSQL server instance are:
Controls general settings, such as memory allocation, default storage
location for new databases, the IP addresses that PostgreSQL listens
on, location of logs, and plenty more. Version 9.4 introduced an
additional file called postgresql.auto.conf, which is created or
rewritten whenever you use the new
ALTER SYSTEM SQL command. The
settings in that file override the postgresql.conf file.
Controls security. It manages access to the server, dictating which users can log in to which databases, which IP addresses or groups of addresses can connect, and which authentication scheme to expect.
If present, maps an authenticated OS login to a PostgreSQL user. People sometimes map
the OS root account to the
postgres superuser account. Each
authentication line in pg_hba.conf can dictate usage of a
If you accepted the default installation options, you find these files in the main PostgreSQL data folder. You can edit them using any text editor, or using the Admin Pack in pgAdmin. Download ...