Backup, Restore and work on PostgreSQL cmd line

Last time, we have a look how to install and set up PostgreSQL on a CentOS machine as well as to create a database. Let’s now see some more details about backups and database restore as well as some commands to master the basic of psql on command line.

Screenshot from 2016-03-31 05-31-15

1.We have created a database called test. Here is an example  how you create a table on Postgres

test=# create table hackers( id int primary key not null, country char(20), number int not null, project varchar(50) );

Screenshot from 2016-03-31 05-41-13

2. You can view your table with the command \d or \d <table name>

Screenshot from 2016-03-31 05-42-38

3. To perform a dump of a database log in as the user which have access to the database and launch the following command.

pg_dump test > db_backup.sql

To restore the same backup on a postgres machine, you need to have the database created first

psql test < db_backup.sql

4. A little work around of psql around the database for example accessing directly the database test with the command psql test and to list all the database can be done with \l

Screenshot from 2016-03-31 06-04-24

5. Let’s say you want to switch between database test and toto. This can be done with \c test and \c toto

Screenshot from 2016-03-31 06-07-05

6. To show all tables inside the database can be done with the command \dt

Screenshot from 2016-03-31 06-08-30

7. The schema of the table can also be seen with the command \d+ <table name> Here, it will be \d+ hackers

Screenshot from 2016-03-31 06-11-19

8. All users and permissions can be viewed with \du

Screenshot from 2016-03-31 06-15-18

9. Databases are create and drop inside the psql command line ending with a semi column

Screenshot from 2016-03-31 06-19-19

10. You can also alter user role with superuser and non superuser

Screenshot from 2016-03-31 06-21-51

11. You can also output the result of a select statement from the psql prompt directly using the following (For example the to output the result of a select statement to ~/SelectStatement.out)  :

postgres=# \o ~/SelectStatement.out - Start logging the output
postgres=# Select * from users;     - The command
postgres=# \o                       - Stop the logging

 

Nitin J Mutkawoa https://tunnelix.com

Blogger at tunnelix.com | Founding member of cyberstorm.mu | An Aficionado Journey in Opensource & Linux – And now It's a NASDAQ touch!

You May Also Like

More From Author