In this post I collected some useful commands for PostgreSQL administration on Linux.

PostgreSQL Interpreter

In order to start an interpreter accepting SQL statements and other PostgreSQL commands, execute:

1

psql -U postgres -h localhost

The database user is specified with -U postgres and -h stands for host name. In this case we assume the database runs on the same machine.

The password must be entered before proceeding. It is also possible to store the password in an environment variable as follows (use with caution and make sure not to expose the variable permanently):

1

export PGPASSWORD="My Password"

You should see a command prompt like this:

psql (13.2 (Ubuntu 13.2-1.pgdg18.04+1), Server 10.16 (Ubuntu 10.16-1.pgdg18.04+1))

postgres=#

The prompt accepts any SQL statements, terminated with semicolons. For example, to list tables in the database, enter:

1

SELECT schemaname, tablename FROM pg_tables;

To change the database, type:

\c database_name

A command to list all tables in the current database is:

\dt

To remove obsolete tuples and optimize the database, execute:

vacuum full;

To quit, enter:

\q

Creating Backups

To create SQL dumps of your databases, use the following command from the Linux shell:

1

pg_dump -U user -h localhost -c --if-exists database_name > backup.sql

The flags -c and --if-exists are optional and will generate drop table if exists commands in the SQL dump.

Restoring Backups

Backups can be restored with the following command:

1

psql -U user -h localhost -d database_name -f backup.sql