How to Work With PostgreSQL Databases

Table of Contents

Listing databases
Dump a database
Dumping all databases
Dump Grants
Delete or Drop a Database
Delete a Grant
Restore a Database
Restore Grant

PostgreSQL is an excellent alternative database engine to MySQL or MariaDB, but managing those databases is very different, especially for those system administrators who are very use to using MySQL from the command line or PHPMyAdmin. Many CMS and applications already know how to interact with PostgreSQL databases out of the box, but like MySQL, still depend on you to maintain those databases.

For this article, we’ll assume that you are SSH-ed into your server as the ‘root’ user, and have installed PostgreSQL server with the default configuration, which requires connecting as the ‘postgres’ user. Therefore, most commands will also take the -U postgres option. These commands will be generally identical between CentOS and Ubuntu across most modern versions but were tested primarily on a CentOS 7 VPS server and a Ubuntu 16.04 server running PostgreSQL 9.2.

Creating databases and grants is rather complex, but if you use cPanel, Plesk, or another control panel that supports PostgreSQL integration, setting this up is straightforward. Making these databases and grants from the command line is outside the scope of this particular article, so we will also assume you already have your databases running and in use.

List Databases in PostgreSQL

It helps to know the exact names of the databases you’ll be working on, as well as do a double check to ensure that you are working on the right server. Let’s list the PostgreSQL databases on our machine first:
psql -l -U postgres

The output for this command is a table of database names, owners, and access privileges. We will use data from the first column when describing database names for future commands. For this article, we’ll use a database called “database_name”.

If you only need database names, and no other information, you can trim away the excess information by performing an exact query for database names:
psql -U postgres -tA -c 'select datname from pg_database;'

This command executes a command (described by the -c flag) of selecting the datname column of the pg_database global table. The -t flag lists only tuples (result rows) and hides excess descriptors like the header and result count, and the -A flag will remove alignment, printing one database per line with no characters in front or behind the names. The output of this command is particularly useful for creating loops for database dumps.

Dump a Database

The dump command will take a full copy of a database, including all tables, schemas, and data, and store it in a single compressed file:
pg_dump -c -Fc -U postgres database_name > database_name.psql

There are a few important flags for this command. -c, or –clean, tells pg_dump to add DROP statements to the output, and -Fc, or –format=custom, compresses and organizes the output to be used with pg_restore later on. The custom format has the greatest flexibility, and advantageously stores the backup in a single file as well.

Dump All Databases

It would likely be superior to bring out each of the databases into its own file by running the above command multiple times. We’ll reference our second listing command, grepping away a few unneeded databases, to make our individual dumps.
for db in $(psql -U postgres -tA -c 'select datname from pg_database;' | grep -E -v “^(template1|template0|postgres)$”); do
echo $db
pg_dump -c -Fc -U postgres $db > $db.psql
done

This for loop will pass a list of all the databases, other than the default templates and postgres database, into the pg_dump command to make a .psql file for each one in the current directory.

Dump Grants

Grants are stored in the PostgreSQL schema. You can dump the schema, thereby getting all of the available grants for the system, using the pg_dump command:
pg_dumpall -U postgres -s > pg.schema.psql

This will dump not only grants, but the rest of the schema of the server as well, including database and table create statements, ownership and role membership, and ACL allocation – basically, everything except the data.

If you need only users (roles) and grants, you can collect those using this command:
pg_dumpall -U postgres -s | egrep -e '^(REVOKE|GRANT)' -e '^(CREATE|ALTER)\ ROLE' > pg.grants.psql

This leverages the full schema dump but takes only the role creation statements and the grant/revoke statements to assemble users and grants.

Delete or Drop a Database

To destroy a database, the syntax is very straightforward. Please take caution when typing out this command, as it does not ask you to confirm!
psql -U postgres -c ‘drop database database_name’

If the database exists, it won’t anymore. There is also a command line wrapper for this same task:
dropdb -U postgres database_name

Delete a Grant

Grants are removed from users by revoking them. It’s important first to know what grants a specific user has, so let’s search for them by dumping the schema and finding lines related to our user.
pg_dumpall -U postgres -s | egrep -e '^(REVOKE|GRANT)' | grep database_user

In my case, there is a grant that looks like this:
GRANT ALL ON DATABASE database_name TO database_user;

To combat this, we will revoke the same descriptor:
psql -U postgres -c ‘revoke all on database database_name from database_user;’

Restore a Database

For databases that were dumped using the pg_dump method used in this article, we can restore that entire database using this command:
pg_restore -U postgres -c -C -O -d database_name database_name.psql

Quite a few more flags needed here! -c or –clean, as before, drops database objects before writing from the file into PostgreSQL. We add this just in case it was not used for the dump command. -C, or –create, will make the database database_name during restore if it does not exist. If the database already exists, the -c flag will destroy it before -C recreates it. Next, there is -O, or –no-owner, which removes the owner from the database. This allows a backup from any source to be changed to the owner of the user doing the restore, which in our case is postgres. -d database_name describes the name of the database to which you will restore. This can be a different name than the one from which the backup was created, if needed. The final argument is the name of our backup file, database_name.psql.

Restore a Grant

Since all grants are stored in our pg.grants.psql file, we need to select only the user that we need to restore. In our case, we will restore database_user:
grep database_user pg.grants.psql | psql -U postgres

Since the pg.grants.psql file has roles and grants, this command will recreate the user with its original privileges and password, and then grant its access to the required databases and schemas.