PostgreSQL - Copy Database
Last Updated :
30 Oct, 2024
Copying a PostgreSQL database is essential for tasks like testing, backup, and database migration. PostgreSQL offers efficient tools for duplicating databases either within the same server or across different servers, ensuring data consistency and integrity.
This guide explains step-by-step methods for copying a PostgreSQL database on the same server and transferring it to a remote server for seamless database backup and migration. Whether you are setting up a development environment or creating redundancy, PostgreSQL simplifies the process for database administrators and developers alike.
Why Copy a PostgreSQL Database?
Database copying is helpful for:
- Testing and Development: Creating a duplicate environment.
- Database Backup: Ensuring data safety through redundancy.
- Database Migration: Transferring databases from one server to another.
Let's go through two main approaches:
- Copying a PostgreSQL database within the same server.
- Copying a PostgreSQL database to a different server.
Copying a PostgreSQL Database Within the Same Server
Sometimes, we may need to create a copy of a PostgreSQL database within the same server, often for testing or development purposes. PostgreSQL makes this simple with the CREATE DATABASE statement using the TEMPLATE option.
Syntax:
CREATE DATABASE target_database
WITH TEMPLATE source_database;
Example
This statement copies the 'source_database' to the 'target_database'. For instance, to copy the dvdrental sample database which is described here and can be downloaded from here, to the 'dvdrental_test database', we use the following statement:
Query:
CREATE DATABASE dvdrental_test
WITH TEMPLATE dvdrental;
This command will create a copy of dvdrental as dvdrental_test.It will take a while to complete copying which depends upon the size of the original database.
Output
After running the above command, we can check if the new database was successfully created by listing all databases in PostgreSQL:
\l
You should see dvdrental_test listed among the databases, confirming that the database copy was successful.
Copying a PostgreSQL Database from One Server to Another
Copying a database between different PostgreSQL servers can be done in several ways. The connection speed between servers can affect the time required, especially for larger databases. One common method is to create a database dump and restore it on the target server.
Steps to Copy a Database
Step 1: Create a Dump file of the source database.
pg_dump -U postgres -d source_database -f source_database.sql
Step 2: Copy the dump file to the remote server.
Step 3: Create a new database in the remote server where you want to restore the database dump:
CREATE DATABASE target_database;
Step 4: Restore the dump file on the remote server:
psql -U postgres -d target_database -f source_database.sql
Example:
Here we will copy the 'dvdrental' database from the local server to the remote server.
Step 1: First, we will dump the 'dvdrental' database into a dump file e.g., 'dvdrental.sql':
pg_dump -U postgres -O dvdrental dvdrental.sql
Step 2: Then we will copy the dump file to a remote server and we will create the 'dvdrental' database on the remote server:
CREATE DATABASE dvdrental;
Step 3: Now, we will restore the dump file that we just created into the remote server:
psql -U postgres -d dvdrental -f dvdrental.sql
Step 4: For high-speed connections between servers or for smaller databases, you can also use the following command:
pg_dump -C -h local -U localuser source_database | psql -h remote -U remoteuser target_database
Step 5: For instance, if one desires to copy the 'dvdrental' database from the localhost to the remote server, you do it as follows:
pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental
Conclusion
Copying a PostgreSQL database within the same server or across servers is a common yet essential task for database administrators and developers. Using these methods, you can efficiently create backup copies, set up testing environments, or perform database migrations. PostgreSQL provides flexible and robust tools to achieve these tasks, ensuring smooth data handling and transfer.
Similar Reads
PostgreSQL - Backup Database All commercial firms and corporations are never 100% free of data threats. Being wary of scenarios like data corruption, host or network failures or even deliberate storage facility destruction is extremely important. Therefore, backing up data is a critical activity that every firm depends on to en
12 min read
PostgreSQL - Create Database Creating a database in PostgreSQL is an important task for developers and database administrators to manage data effectively. PostgreSQL provides multiple ways to create a database, catering to different user preferences, whether through the command-line interface or using a graphical interface like
5 min read
PostgreSQL ALTER DATABASE The PostgreSQL ALTER DATABASE statement is a powerful tool used for modifying an existing database.  The features of a database, once created can be changed using the ALTER DATABASE statement. Let's explore the syntax of PostgreSQL ALTER DATABASE, the various actions that can be performed, and pract
3 min read
PostgreSQL - Show Databases In PostgreSQL, viewing a list of all databases on a server requires specific commands, as it doesn’t support a direct SHOW DATABASES statement like MySQL. Instead, you can use the \l or \l+ commands in psql or query the pg_database view to display all databases. In this article, we will guide us thr
3 min read
PostgreSQL - Rename Database Renaming a PostgreSQL database is a simple yet essential task for developers and database administrators. Whether we're migrating data, restructuring our database, or just managing our server efficiently, the ability to rename a PostgreSQL database can streamline our workflow. In this article, we wi
4 min read
PostgreSQL - Restore Database Restoring a PostgreSQL database is an essential process for data recovery, especially when dealing with data loss, corruption, or unintentional errors. The ability to efficiently restore a database ensures that an organization can maintain data integrity, availability, and consistency. In this artic
5 min read
PostgreSQL - Loading a Database In this article we will look into the process of loading a PostgreSQL database into the PostgreSQL database server. Before moving forward we just need to make sure of two things: PostgreSQL database server is installed on your system. A sample database. For the purpose of this article, we will be us
3 min read
PostgreSQL - Copying Data Types When working with PostgreSQL, you can define a variable that directly references the data type of a column in a table or the data type of another variable. This feature is useful when you want to maintain consistency and avoid repetitive changes to your code whenever the data type of a column is alt
3 min read
PostgreSQL - Connect and Access a Database In this article, we will learn about how to access the PostgreSQL database. Once the database is created in PostgreSQL, we can access it in two ways using: psql: PostgreSQL interactive terminal program, which allows us to interactively enter, edit, and execute SQL commands.pgAdmin: A graphical front
3 min read
PostgreSQL - COMMIT The COMMIT command in PostgreSQL is important for saving the changes made during a transaction. Without executing a COMMIT, all the data manipulation operations performed within the transaction will be lost once the session ends. It ensures that the changes made to the database are permanent and vis
4 min read