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