When you need to create a database dump and restore it (especially for a new environment or migrating an app), it's worth asking yourself a few important questions before you start:

• Which database(s) do I need to back up?
• Where are multimedia files stored? (Are they kept on disk, in S3, or another external service?)
• Does the dump alone capture all critical application data, or do I need additional steps?

These considerations help prevent surprises and make the restoration process much smoother.

Recently, I did my first PostgreSQL database dump and restore. Here’s what I learned and what you should keep in mind:

Database Dump

To back up the PostgreSQL database, I used the pg_dump command with the following setup:

pg_dump -h localhost -U db_user -d db_name -F c --no-owner -f ~/dump_$(date +%Y%m%d).dump

• The --no-owner flag is very important—it prevents warnings during the restoration. Without it, the dump records who owns each table (for example, user dev), and this can cause errors like role 'dev' does not exist if you restore on another machine.
• The -F c flag makes a compressed, "custom" format dump, which is smaller and allows for selectively restoring data.

Restore and Application Data

To restore the backup, I first created a new database:

createdb new_database
pg_restore -d new_database ~/dump_20260114.dump

This part was straightforward. After restoring, make sure to verify your application—check if all data is in place and working.

What About Media Files?

When I checked the admin panel in my app, I noticed that images were missing. This is because:

• In our app, we don't use S3; all files are stored locally on disk using Active Storage.
• The database dump only contains metadata about the files (active_storage_blobs and active_storage_attachments tables).
• The files themselves are separate and must be copied manually.

Therefore, in my case, I also had to back up the storage folder in addition to the database dump. Once I did that, everything worked as expected!

Efficiently Transferring Large Folders

Our storage folder was about 8GB, and the server had very little free space. I couldn’t create an archive on the server first. Luckily, there's a way to compress and download the folder directly (without saving the archive locally on the server):

ssh server "cd /path/to/app && tar czf - storage" > ~/Downloads/storage_backup.tar.gz

• Here, tar czf - storage streams the archive straight to your local machine.

Resources for Further Reading:

- pg_dump documentation

- pg_restore documentation

- tar documentation

In summary:

Before dumping your database, think about all areas where your app stores data, especially multimedia. Back up both your database and any storage directories or external resources for a complete and functional restore.