14 Postgres commands for basic administration Print

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

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

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;

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

10. Set super privileges

ALTER USER username WITH SUPERUSER;

11. List users

\du

12. Deleting user

DROP USER nomusr

13. Getting help

\?

or

\h

14. Exit from Postgres console

\q