14 commands for basic Postgres administration

psql is a CLI client for Postgres administration, psql allows:

– Connect to the server
– Create DB
– Admin users
– Execute SQL queries and perform administration tasks in general.

Note:This procedure has been executed on Debian 9.9 and Postgres 9.6

In Debian we have the following directory structure for the Postgres configuration files.

$ cat /etc/postgresql
└── 9.6
     └── main
         ├── environment
         ├── pg_ctl.conf
         ├── pg_hba.conf
         ├── pg_ident.conf
         ├── postgresql.conf
         └── start.conf

1. Connect to Postgres with the admin or root user

$ sudo -u postgres psql

2. List BD


3. Use/Select a database

\c dbname;

4. List tables


5. Create database

CREATE DATABASE dbname OWNER username;

The public schema is created with postgres owner by default, so we must change it to username (before we must select the DB dbname):

ALTER SCHEMA public OWNER TO username;

6. Delete database


7. Import a database

$ psql username  -h hostname -d dbname < dump.sql

8. Create user


9. Connect to the DB with the created user

$ psql -U username -h -d database-name

For connecting from the LAN, you need to modify the postgresql.conf and pg_hba.conf files. Look for listen_address in the postgresql.conf and add the IP addresses where the Postgres server is listen on, for example:

listen_addresses = 'localhost,'

Look for IPv4 local connections section in the pg_hba.conf file and add

# TYPE  DATABASE        USER            ADDRESS             METHOD
host    all             all        md5

With the previous rule we establish that any user can connect to any database from our local area network:, if we want to restrict access to a single user and to a database we can do it as follows:

host    databasename             username        md5

10. Set super privileges


11. List users


12. Deleting user

DROP USER nomusr

13. Getting help




14. Exit from Postgres console


Further reading

– man psql

YouTube video

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.