The following list includes some of the most commonly used commands used to interact with PostgreSQL. These commands should be run on the command line, and not inside the psql console.
Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specified via the -O option, if the executing user has appropriate privileges.
createdb is a wrapper around the SQL statement
CREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server.
dropdb destroys an existing PostgreSQL database. The user who executes this command must be a database superuser or the owner of the database.
dropdb is a wrapper around the SQL statement DROP DATABASE. There is no effective difference between dropping databases via this utility and via other methods for accessing the server.
For example, if we wanted to drop the database we created earlier, we would use the following command:
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.
pg_dump does not block other users accessing the database (readers or writers).
It also has some other uses as well. If you want to see what SQL statements would be used to create the schema and data in a database, the
pg_dump command will do what you want (and more). The following command will print out this information as SQL statements:
$ pg_dump --inserts --clean sql_book
The output of this command can be saved to a file using command output redirection:
$ pg_dump --inserts --clean sql_book > dump.sql
There will be some SQL statements in the dump to configure database settings and set the permissions on the tables. We aren't going to cover these functions in this course, so feel free to look through them but don't worry about their specifics.
We can use the text file created by
pg_dump to restore a database. We do this by reading
a file into the
psql command. If we were to use the same file as used in the previous example,
then the command we would use would be:
psql -d sql_book < dump.sql
The -d option specifies which database to connect to. The use of
< reads the contents
of the file dump.sql into the
psql command. This will allow us to restore the data
of our database if it has been lost or altered after using
sql_book database has been removed with
dropdb, it first needs to be recreated
to use the above command. So first run:
Then we can restore the data that was in that database using the dump.sql file and
Listed above are some core commands that we will use often in the remainder of this book. There are many more useful Postgres commands. We leave it up to the reader to find such commands through the official documentation when they have use of them.