This video is about backup a PostgreSQL database. Using cmd is more easier (what I think) to backup and also restore. It is very usefull if you continuously. Export a PostgreSQL database. You can export a PostgreSQL database to a file by using the pgdump command line program, or you can use phpPgAdmin. Method #1: Use the pgdump program. To export a PostgreSQL database using the pgdump program, follow these steps. Access the command line on the computer where the database is stored. Pgsql9/bin/pgdump -Fc -v -h 127.0.0.1 -p 20293 -U whd whd whdpgdump.backup Enter whd if prompted for a database password. A backup file will be created in / Library/WebHelpDesk/ with the name whd.pgdump.backup. Restore the PostgreSQL database. Open a command prompt.
Summary: in this tutorial, you will learn how to copy a PostgreSQL database on the same server or from a server to another.
PostgreSQL copy database within the same server
Sometimes, you want to copy a PostgreSQL database within a database server for testing purposes.
PostgreSQL makes it easy to do it via the CREATE DATABASE statement as follows:
This statement copies the sourcedb to the targetdb. For example, to copy the dvdrentalsample database to the dvdrental_test database, you use the following statement:
Dbeaver Pg_dump Not Found
Depending on the size of the source database, it may take a while to complete copying.
If the dvdrental database has active connections, you will get the following error:
The following query returns the active connections:
To terminate the active connections to the dvdrental database, you use the following query:
After that you can execute the CREATE TABLE WITH TEMPLATE statement again to copy the dvdrental database to dvdrental_test database.
PostgreSQL copy database from a server to another
There are several ways to copy a database between PostgreSQL database servers.
If the size of the source database is big and the connection between the database servers is slow, you can dump the source database to a file, copy the file to the remote server, and restore it:
First, dump the source database to a file.
Second, copy the dump file to the remote server.
Third, create a new database in the remote server:
Finally, restore the dump file on the remote server:
Copying the dvdrental database example
The following steps illustrate how to copy the dvdrental database from the local server to the remote server.
First, dump the dvdrental database into a dump file e.g., dvdrental.sql:
Second, copy the dump file to the remote server.
Third, create the dvdrental database on the remote server:
Fourth, restore the dvdrental.sql dump file in the remote server:
Dbeaver Pg_dump Version
In case the connection between servers are fast and the size of the database is not big, you can use the following command:
For example, to copy the dvdrental database from the localhost server to the remote server, you do it as follows:
In this tutorial, you have learned how to copy a PostgreSQL database within a database server, or from a database server to another.