How to Copy a PostgreSQL Database Safely | DbSchema

Postgresql • 13-Apr-2019

by DbSchema Team

Using SQL

1.Make sure that no other user is using the database by killing all connections:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

2.Create a new database from the original template:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Replace originaldb and newdb.

Another method is to use psql for creating a database copy. Access the PostgreSQL command and execute:

createdb -O ownername -T originaldb newdb

✓ Create and manage your database schema visually through a user-friendly graphical interface.

✓ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

✓ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

✓ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

✓ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

✓ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

✓ Generate HTML5 documentation that provides an interactive view of your database schema.

✓ Include comments for columns, use tags for better organization, and create visually reports.