task_004_managing_database

Managing Database

• Managing Database
  • Create
  • Show all databases
  • Connect to required database
  • Drop a database
  • Dump
  • Restore

Create

sql-createdatabase

To create a database with owner as admin

CREATE DATABASE sales OWNER admin
CREATE DATABASE

Show all databases

Doc

To get the list of all databases

postgres=# \l

You can also use

SELECT datname FROM pg_database;

Connect to required database

• First see all databases

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres

• To connect to right db

postgres=# \c postgres
psql (13.2, server 13.3 (Debian 13.3-1.pgdg100+1))
You are now connected to database "postgres" as user "postgres".

Drop a database

Docs

• You can use the following command

DROP DATABASE <database_name>

Dump

Docs

To dump the database you can use

pg_dump -h <hostname> -p 5432 --username=<username> --db <databasename> > outputfile.sql 

Restore

Docs

• To restore the database dump you can use

psql -h <hostname>  -p 5432 --username=<username> --db <databasename> -f  filename.sql