PostgreSQL is a high-performance, scalable, and widely-used open-source relational database system. In this article, we provide a detailed guide covering everything from creating to backing up PostgreSQL databases. We will explore how to use DBeaver for database creation, management, and query execution, as well as introduce efficient backup procedures using pg_dump. Additionally, we present practical approaches for backing up databases in containerized environments such as Docker and Kubernetes. By following this guide, you will gain a comprehensive understanding of how to effectively manage and safeguard PostgreSQL databases across various environments.
* Connecting with DBeaver
* First, navigate to the menu and select “Database > New Database Connection”, then choose PostgreSQL from the list of database types.
> Host: The domain or IP address of the database server
> Port: Connection port (default: 5432)
> Database: Name of the database (default: postgres)
> Username / Password: Login credentials (password set for the postgres account during installation)
> Host: The domain or IP address of the database server
> Port: Connection port (default: 5432)
> Database: Name of the database (default: postgres)
> Username / Password: Login credentials (password set for the postgres account during installation)
* Test Connection: Verify that the entered information allows a successful connection.
* In the menu, select SQL Editor > New SQL Script to open a script window for executing SQL queries.
* Write your query in the SQL editor and press “Control + Enter” to execute it.
* Creating a Database & Database Administrator User
* The example below demonstrates how to create a test database and assign a superadmin user, test_admin, to manage the newly created database.
-- Create database
CREATE DATABASE test;
-- Create user
CREATE USER test_admin WITH PASSWORD 'secure_password';
-- Make a user the manager of a table
ALTER DATABASE test OWNER TO test_admin;
* If you want to grant the user superuser privileges to manage the entire PostgreSQL system, you can use the query below. However, it is not recommended to elevate a user to superuser solely for the convenience of development or management.
# Create
CREATE EXTENSION vector;
# Drop
DROP EXTENSION IF EXISTS vectors;
* You can verify the connection by logging in again using the credentials of the newly created database and user.
* DB Backup
* You can use pg_dump for backups. If PostgreSQL is installed locally, you can run the following command directly in the local shell.
# pg_dump -U {user name} {db name} > {backup file name}
postgres-user:~$ pg_dump -U test_admin test > backup.sql
* Backing Up a Database Running in a Container
* If PostgreSQL is running inside a container, you first need to access the container’s shell. When performing a backup, a backup file is generated. However, container images typically grant limited permissions to the user for security reasons, which may prevent file creation. To resolve this, it is common to navigate to the /tmp directory, as it allows regular users to create files.
# Check the container id
user:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS
c3f279d17e0a postgresq "/bin/bash" 5 minutes ago Up 5 minute
# Enter the Container : docker exec -it <container_name_or_id> /bin/bash
user:~$ docker exec -it c3f279d17e0a /bin/bash
# Change directory
c3f279d17e0a:~$ cd /tmp
# DB backup : pg_dump -U {user name} {db name} > {backup file name}
c3f279d17e0a:/tmp$ pg_dump -U test_admin test > backup.sql
# Exit from the Docker container to the local host
c3f279d17e0a:/tmp$ exit
user:~$
* To retrieve the generated backup file (backup.sql) to your local host, use a Docker command to copy it from the container.
# Copy the backup.sql file from the container to the local directory
user:~$ docker cp c3f279d17e0a:/tmp/backup.sql .
* Database Restore
* Restoration can be done using psql. If PostgreSQL is installed locally, you can run the following command directly in the local shell.
# psql -U {user name} {db name} < {backup file name}
postgres-user:~$ psql -U test_admin test < backup.sql
* Restoring a Database Running in a Container
# Check the container id
user:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS
c3f279d17e0a postgresq "/bin/bash" 5 minutes ago Up 5 minute
# Copy the backup.sql file from the local directory to the container
user:~$ docker cp backup.sql c3f279d17e0a:/tmp
# Enter the Container : docker exec -it <container_name_or_id> /bin/bash
user:~$ docker exec -it c3f279d17e0a /bin/bash
# Change directory
c3f279d17e0a:~$ cd /tmp
# DB restore : psql -U {user name} {db name} < {backup file name}
c3f279d17e0a:/tmp$ psql -U test_admin test < backup.sql
# Exit from the Docker container to the local host
c3f279d17e0a:/tmp$ exit
user:~$
