Remote access to PostgreSQL

Knex

Use knex migrate to create and edit tables in the database, use knex seed to add values to tables in the database.

psql

Install psql on your local system, then you can connect a psql command line session to your Render.com database using the "PSQL command" invocation string found in the database/info tab in your Render.com admin interface:

pg_dump

Install pg_dump on your local system, then you can use it to dump the remote database into your local system using the "External Connection String" from the Render.com database admin user interface (see illustration above). Note that when you paste this string into your command line, you need to add ?ssl=true at the end of it, so your command will look like this:

$ pg_dump <External Connection String>?ssl=true > dumpfilename.sql

How to restore the database from a dump file

This is tricky or simple, depending on your tolerance for a blizzard of error messages.

The recommended (simple) way to restore a database according to the customer support line at Render.com is to pipe a dump file from your system up to the remote database using psql like this:

1. Use psql to drop all the tables you wish to restore, e.g. "drop table test_table;" The tables of interest are listed below.
2. Download a backup file from the Backups tab, then restore it by something like (substitute the right strings for the -dc, PASSWORD], -h and -U arguments):

$ gzip -dc 2021-01-15T06_33Z.sql.gz | PGPASSWORD=XXX psql -h oregon-postgres.render.com -U XXX_user test_database

This will result in pages of error messages, which you should ignore.

If you prefer to perform a restore without any error messages, you will need to unzip the dump file and edit it considerably.

1. Delete everything in the file except the COPY commands and their data.
2. Paste this into the top of the file:

delete from access_tokens;
delete from uploads;
delete from period_summaries;
delete from documents;
delete from projects;
delete from users;
delete from agencies;
delete from subrecipients;
delete from application_settings;
delete from reporting_periods;  

3. Then reorder the COPY commands into the reverse of this order, so the first COPY command will be reporting_periods and the last will be access_tokens.

4. Then pipe the resulting SQL file into psql:

$ <psql command> < editeddumpfilename.sql

5. At this point the auto-increment fields will still be messed up. You can fix them one at a time with:

SELECT setval('uploads_id_seq', (SELECT MAX(id) FROM uploads));

Or you can fix them all at once using the method documented at https://wiki.postgresql.org/wiki/Fixing_Sequences.