In this post I collected some useful commands for PostgreSQL administration on Linux.
In order to start an interpreter accepting SQL statements and other PostgreSQL commands, execute:
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):
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:
SELECT schemaname, tablename FROM pg_tables;
To change the database, type:
A command to list all tables in the current database is:
To remove obsolete tuples and optimize the database, execute:
To quit, enter:
To create SQL dumps of your databases, use the following command from the Linux shell:
pg_dump -U user -h localhost -c --if-exists database_name > backup.sql
--if-exists are optional and will generate
drop table if exists commands in the SQL dump.
Backups can be restored with the following command:
psql -U user -h localhost -d database_name -f backup.sql