More PostgreSQL Commands

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.

createdb

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

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:

dropdb sql_book

pg_dump

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.

restoring a database with psql

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 pg_dump.

If the sql_book database has been removed with dropdb, it first needs to be recreated to use the above command. So first run:

createdb sql_book

Then we can restore the data that was in that database using the dump.sql file and psql.

Listed above are some core commands that we will use often in the remainder of this book. There are many more useful PostgreSQL commands. We leave it up to the reader to find such commands through the official documentation when they have use of them.