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.
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) );
2. You can view your table with the command \d or \d <table name>
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
5. Let’s say you want to switch between database test and toto. This can be done with \c test and \c toto
6. To show all tables inside the database can be done with the command \dt
7. The schema of the table can also be seen with the command \d+ <table name> Here, it will be \d+ hackers
8. All users and permissions can be viewed with \du
9. Databases are create and drop inside the psql command line ending with a semi column
10. You can also alter user role with superuser and non superuser
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
