Database Management - supertypeai/sectors-kb GitHub Wiki

This section focuses on how we manage, optimize, and maintain the database.

1. Export Data with pg_dump

   • Data is exported from Supabase using the pg_dump command-line tool.

   • Example command:

     pg_dump -d postgresql://<supabase_connection_string> --table=<table_name> --format=c -f <output_file>.custom  

     Replace:

     • <supabase_connection_string>: Supabase database URL.
     • <table_name>: Name of the table to export (e.g., idx_company_profile).
     • <output_file>: Desired name for the dump file.

   • Why use .custom? The .custom format (--format=c) is a compressed, non-plain SQL archive that requires pg_restore for restoration. It allows for more efficient storage and flexibility during restoration compared to plain SQL dumps, which can only be restored using psql.

2. Import Data with pg_restore

   • Data is imported into Neon using the pg_restore tool.
   • Example command:

     pg_restore -d postgresql://<neon_connection_string> <output_file>.custom  

     Replace:
     • <neon_connection_string>: Neon database URL.
     • <output_file>: Name of the dump file created in step 1.

3. Check for Errors

   • During the pg_restore process, check for errors reported in the terminal.
   • Errors usually indicate the issue type and the specific query causing it.

   Common Error - transaction_timeout

   unrecognized configuration parameter "transaction_timeout"  
   

   This error occurs because transaction_timeout is not recognized in Neon. You can safely ignore this error, as it is not critical to the table creation process.

4. Resolve Errors and Re-run Queries

   • Fix the root cause of any errors. For example, create missing roles in Neon.
   • Manually re-run the problematic query commands in a SQL editor to verify fixes.

In Supabase, roles like postgres exist by default, but Neon does not include a postgres role. Instead, it uses the neondb_owner role. This can cause errors such as:

role "postgres" does not exist

To resolve this, you need to create roles in Neon with passwords. For example:

• Role Creation in Supabase:

  CREATE ROLE anon;

• Role Creation in Neon:

  CREATE ROLE anon PASSWORD '<password>';

To simplify remembering passwords, use a combination of the neondb_owner password and the role name. For instance, if the neondb_owner password is secure_password and you want to create a role named anon, the password for anon should be:

CREATE ROLE anon PASSWORD 'secure_password@anon';

Finally, make sure to replace any references to the postgres role (which exists by default in Supabase) with neondb_owner in your queries when migrating to Neon.