Postgres Database Management - Copy, Reset, and Populate Commands

––– views

1 min read

February 01, 2023 March 10, 2023

The following few commands I’ve listed have been a lifesaver many times in my career, I’ve curated these commands from many different forums, their official documentation, and my personal experience.

⚠️

These commands work only on Mac/Linux systems, they didn’t work on Windows.

List of the commands

What you need to do first is make sure you have the psql CLI installed on your machine

1. Copy the main DB to database_dump

These values can be found in your database provider, in my case I’m using render.com

PGPASSWORD="DB_PASS" pg_dump -h frankfurt-postgres.render.com -U DB_USER DB_NAME \ -n public --no-owner > database_dump.sq

The values you need

DB_PASS, DB_USER, DB_NAME, and the URL for your database host in my case is frankfurt-postgres.render.com

2. Resetting the local DB

There are many commands for doing this, however, for me, only one very weird command worked properly.

First in the terminal run psql name-of-database

With this, you now can query/change your database, run the following command in order to delete all the data, and reset the local DB.

JSON 
DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema() AND tablename != 'spatial_ref_sys') LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$;

3. Populate the local DB

PGPASSWORD="your password" psql -h localhost -u postgres DATABASE_NAME < database_dump.sql

My example

PGPASSWORD=damjan psql -h localhost -U postgres mylocaldb < database_dump.sql

📢

Make sure you’re in the same directory where you created the .dump file

Conclusion

If everything goes as it should, your local DB should now be an exact replica of the production one. Hope this post helped you figure out this tedious process.

If you’re using Prisma and would like to know how I migrate my database painlessly checkout this tutorial

To comment please authenticate