sql Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Copying the dvdrental database example Third, create a new database in the remote server: CREATE DATABASE targetdb Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql )įinally, restore the dump file on the remote server: psql -U postgres -d targetdb -f sourcedb. Second, copy the dump file to the remote server. sql Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) pg_dump -U postgres -d sourcedb -f sourcedb. 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:įirst, dump the source database to a file. There are several ways to copy a database between PostgreSQL database servers. PostgreSQL copy database from a server to another To terminate the active connections to the dvdrental database, you use the following query: SELECT pg_terminate_backend (pid)Īfter that you can execute the CREATE TABLE WITH TEMPLATE statement again to copy the dvdrental database to dvdrental_test database.
WHERE datname = 'dvdrental' Code language: JavaScript ( javascript ) The following query returns the active connections: SELECT pid, usename, client_addr If the dvdrental database has active connections, you will get the following error: ERROR: source database "dvdrental" is being accessed by other usersĭETAIL: There is 1 other session using the database. WITH TEMPLATE dvdrental Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql )ĭepending on the size of the source database, it may take a while to complete copying. For example, to copy the dvdrental sample database to the dvdrental_test database, you use the following statement: CREATE DATABASE dvdrental_test This statement copies the sourcedb to the targetdb. WITH TEMPLATE sourcedb Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) PostgreSQL makes it easy to do it via the CREATE DATABASE statement as follows: CREATE DATABASE targetdb Sometimes, you want to copy a PostgreSQL database within a database server for testing purposes. PostgreSQL copy database within the same server
#CLONE POSTGRES DATABASE HOW TO#
Summary: in this tutorial, you will learn how to copy a PostgreSQL database on the same server or from a server to another.
0 kommentar(er)
