Losing an entire day to a finicky dev environment that doesn’t mirror production is a common frustration. This guide provides a direct, exhaustive path to replicate your production database into a safe development project. Because things can go wrong, it’s also loaded with troubleshooting solutions for the most common errors.

This article covers the database replication process. Part 2 will then guide you through configuring your Flutter application and social logins for the new environment.

Prerequisites: Before you begin, ensure you have PostgreSQL installed on your local machine (this provides the pgAdmin application and PowerShell command-line tools).

This guide provides instructions for a Windows environment; macOS and Linux users will need to adapt the command-line steps.

Step 1: Create the Development Supabase Project

Objective: To create a new, independent Supabase project that is a functional copy of your production database’s application data, tables, functions, and policies.

1. Navigate to the Supabase Dashboard

https://supabase.com/dashboard/organizations) and follow these steps:

2. Create a new organization

In the organization dropdown, select “+ New organization”.

Get Saropa Contacts News’s stories in your inbox

Join Medium for free to get updates from this writer.

Subscribe

Name it [YourAppName]-Dev. This provides a new free project slot.

Press enter or click to view image in full size

This is your organization within Supabase. For example, you can use the name of your company or department.

3. Create a New Project

Inside the new organization, click “New Project”.

Press enter or click to view image in full size

Your project will have its own dedicated instance and full Postgres database.

An API will be set up so you can easily interact with your new database.

Configure the project:

• Name: [YourAppName]-DEV
• Database Password: Create a strong, new password and save it in a secure location.
• Region: Select your preferred region.
• Pricing Plan: Select the Free plan.

Click “Create new project” and wait for it to be provisioned.

4. Location the connections settings

Press enter or click to view image in full size

You can find Project connect details by clicking ‘Connect’ in the top bar

Press enter or click to view image in full size

Get the connection strings and environment variables for your app

Step 2: Create a “Public Schema Only” Backup from Production

1. Connect to Production (Source)

Open pgAdmin and connect to your production Supabase database.

Press enter or click to view image in full size

NOTE: This screen shows the target database server — you MUST select the Source (production) server

2. Select Backup

In the object browser, right-click the postgres database and select Backup….

Make sure to select the database, not the server

3. Choose Backup Options

In the “Backup” window:

• General Tab: Name the file public_schema_backup.sql and save it to your Desktop.
• Format Tab: Set Format to Plain.

• Dump Options Tab: Ensure Pre-data, Data, and Post-data are all ON.

• Objects Tab: Check the box only for the public schema. Ensure no others are checked.

4. Click Backup.

Backups can be expected to take 3–10 minutess (or more), depending on the databse size and the internet connnection.

3: Clean the Development Database

1. In pgAdmin, connect to your new development Supabase database.
2. Right-click the public schema and select Query Tool.

🔴 CRITICAL WARNING: Before running this next command, triple-check that you are connected to your DEVELOPMENT database. This command will permanently erase all data in the public schema. There is no undo.

🔴 ANOTHER CRITICAL WARNING: Check you are running full server backups and they have not been failing.

Paste and execute the following SQL code:

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

🔴 As the AI would say: Always use code with caution.

A success message should appear immediately.

Step 4: Restore the Backup Using PowerShell

1. Open the PowerShell application.
2. Navigate into the PostgreSQL binary directory.
3. Generated powershell

cd “C:\Program Files\PostgreSQL\16\bin”

Note: The version number ‘16’ may be different on your machine. Check your C:\Program Files\PostgreSQL directory for the correct version and update the command if needed.

Construct the restore command in a text editor.

.\psql.exe -h “[DEV_DB_HOST]” -p “5432” -U “postgres” -d “postgres” -f “[PATH_TO_BACKUP_FILE]”

• Replace [DEV_DB_HOST] with the Host from your dev project’s settings.
• For [PATH_TO_BACKUP_FILE], right-click your .sql file on the Desktop and select “Copy as path”.

1. Paste the complete command into PowerShell and press Enter.
2. When prompted, type the password for your development database and press Enter.

Note: For security, your password will not be visible as you type.

Troubleshooting 🛡️

Problem: The restore fails with errors like ERROR: schema “auth” already exists or ERROR: permission denied for schema storage.

• Cause: You did not back up only the public schema.
• Solution: Delete the failed backup file and re-do Step 2, ensuring only the public schema is checked.

Problem: The command fails with an error about ‘Execution Policy’.

• Cause: Your system’s PowerShell security is blocking the command.
• Solution: You may need to open PowerShell as an Administrator and run Set-ExecutionPolicy RemoteSigned -Scope Process to allow the command for your current session.

Problem: The connection times out or is refused.

• Cause: A firewall on your computer or network may be blocking the connection to port 5432.
• Solution: Check your firewall settings to ensure outbound connections on TCP port 5432 are allowed.

Problem: The command line says ‘C:\Program’ is not recognized.

• Cause: You did not first navigate into the PostgreSQL bin directory.
• Solution: You must successfully run the cd command shown in Step 4.

Problem: My tables are created but there is no data inside.

• Cause: The Data switch in the “Dump Options” tab was turned OFF during backup.
• Solution: Re-create the backup, ensuring all three switches are enabled.

Step 5: Verification

Your server should be fully duplicated to a new database and server. As it was created under the FREE tier, you cannot expect backups and other paid features.

If something goes wrong, review the troubleshooting steps again, and make sure you are connected to the correct servers/database.

If all steps were followed correctly, you should now have an isolated replica of your production database. The next step is connecting your application, so in Part 2, we connect our app to this new development server.

“The ability to take data, to be able to understand it, to process it, to extract value from it, to visualize it, to communicate it — that’s going to be a hugely important skill in the next decades.” — Hal Varian

References

1. pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world. https://www.pgadmin.org/
2. PowerShell is a cross-platform task automation solution made up of a command-line shell, a scripting language, and a configuration management framework. PowerShell runs on Windows, Linux, and macOS. https://learn.microsoft.com/en-us/powershell
3. Supabase is an open-source backend-as-a-service (BaaS) platform built on top of PostgreSQL, a powerful and trusted relational database. https://supabase.com/dashboard/organizations

Final Word 🪅

Your feedback is essential to us, and we genuinely value your support. When we learn of a mistake, we acknowledge it with a correction. If you spot an error, please let us know at blog@saropa.com and learn more at saropa.com.

About Saropa

Over our multi-decade journey in tech, we’ve worn many hats, from coding national bank systems, managing industry-leading dev teams, and delivering international emergency projects. We’re passionate about sparking curiosity and understanding of complex topics.

saropa.com