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

\l

3. Use/Select a database

\c dbname;

4. List tables

\dt

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

DROP DATABASE dbname;

7. Import a database

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

8. Create user

CREATE USER username WITH PASSWORD 'MYPASS';

9. Connect to the DB with the created user

$ psql -U username -h 127.0.0.1 -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, 192.168.100.29'

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

# TYPE  DATABASE        USER            ADDRESS             METHOD
host    all             all         192.168.100.1/24        md5

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

host    databasename             username         192.168.100.1/24        md5

10. Set super privileges

ALTER USER username WITH SUPERUSER;

11. List users

\du

12. Deleting user

DROP USER nomusr

13. Getting help

\?

\h

14. Exit from Postgres console

\q