Accessing a PostgreSQL Database in a Docker Container
Sometimes, you’ll need to access a PostgreSQL database directly inside of a Docker Container. This article will give you some commands to use for this.
ProGet is a free private Docker registry and package server that you host on your own infrastructure. Install in minutes on Windows or Linux/Docker, and start using today!
Backup and Restore
Backing up a PostgreSQL database uses the pg_dump command, and restoring it uses the psql command. The commands below will work as-is, but if you want to know what each piece does, continue reading.
# Back up database
docker exec -u postgres proget-postgres pg_dump -Cc | xz > proget-backup-$(date -u +%Y-%m-%d).sql.xz
# Restore database from backup
xz -dc proget-backup-YYYY-MM-DD.sql.xz | docker exec -i -u postgres proget-postgres psql –set ON_ERROR_STOP=on –single-transaction
| docker exec | This runs a command inside a Docker container. |
| -u postgres | We want to run the command as the postgres user because the docker exec command defaults to using the root user and the root user does not have access to the database. |
| proget-postgres | This is the name of the Docker container running PostgreSQL. If you created the container with a different name, substitute it here. |
| pg_dump | pg_dump is the PostgreSQL database backup utility. It converts a database to an SQL script. It can also convert to some other formats, but we aren’t going to use those right now. |
| -Cc |
Equivalent to –create –clean.
–create tells pg_dump to include tables, views, and functions in the backup, not just the data contained in the tables. –clean tells pg_dump to start the SQL script by dropping the data that is currently in the database. This makes it easier to restore in one step. |
| | xz | We run the SQL script through a compression program (in this case, xz) to reduce the amount of storage space taken by the backup and to reduce the time it takes to transfer the backup over the network. This is optional, and other commands can be used in place of xz, such as gzip and bzip2. To get even better compression, the -9 and/or -e options can be specified. -9 makes xz use much more memory, and -e makes xz use much more CPU power. However, the default compression level should be good enough in nearly every case. |
| > proget-backup-$(date -u +%Y-%m-%d).sql.xz | The compressed SQL script is currently being written on the standard output, so we redirect it to a file with a name like proget-backup-YYYY-MM-DD.sql.xz. This will be placed in the current directory when you run the command. |
| xz -dc proget-backup-YYYY-MM-DD.sql.xz | |
Because we compressed the SQL script in the previous command, we need to decompress it before we can restore the backup. -dc is equivalent to –decompress –stdout.
–decompress tells xz that we want to decompress the file, not compress it again. –stdout tells xz that it should write the contents of the file to the standard output and not delete the .xz file. Without this, xz will write the output to a file named proget-backup-YYYY-MM-DD.sql and delete the compressed version. gzip and bzip2 both use the same meaning for -dc as xz. |
| -i | This tells Docker to keep the standard input open so the SQL script can be sent to psql. |
| psql | This is the PostgreSQL interactive SQL command line. In this case, we’re using it to run the SQL script containing the database backup. |
| –set ON_ERROR_STOP=on | Tells psql to stop executing the restore if an error occurs. |
| –single-transaction | Tells psql to run the entire restore in one transaction so that any problem that causes it to stop doesn’t leave the database in an inconsistent state. |
Running SQL Interactively
To start an interactive SQL session, run this command:
docker exec -tiu postgres proget-postgres psql
To end the session, press Ctrl+D or type \q (backslash Q) and press enter.
Here are some examples of useful SQL queries:
— Show proxy-related configuration settings
select * from “Configuration_GetConfiguration”() where “Key_Name” like ‘Proxy.%’;
— Switch to the built-in user directory
select “Configuration_SetValue”(‘Web.UserDirectoryId’, ‘1’);
— Retrieve the last 25 messages in the error log
select “LogMessage_Id”, “Logged_Date”, “Message_Text” from “LogMessages_GetMessages”(25);
— Get details (for example, a stack trace) for log message 3
\a
select convert_from(“Details_Bytes”, ‘utf-8’) from “LogMessages_GetMessage”(3);
\a
