From Pexels Andrea Piacquadio

PostgreSQL Transfer Workflow Access and Credentials

It turns out to be surprisingly painful getting a database replicated when you have to do it using a heavily locked down Windows PC.

Read-only database user

On the target server, create a dedicated read-only user for analysis work rather than using a privileged account.

Typical permissions:

GRANT CONNECT ON DATABASE researchdb TO pgresearch;
GRANT USAGE ON SCHEMA ons TO pgresearch;
GRANT SELECT ON ALL TABLES IN SCHEMA ons TO pgresearch;

Why?

• Cannot accidentally modify data.
• Safer for scripts and pipelines.
• Separates analysis from administration.

Password storage

Now, I’m comfortable enough on Linux using .pgpass (although I can forget to use chmod to set to 600), however, it’s a bit more fiddly on Windows, because you need to set the file %APPDATA%\postgresql\pgpass.conf, and it seemed to be the case that Windows Explorer was hiding this. I was able to locate it using Git Bash under C:\Users\Paul\AppData\Roaming\postgresql\pgpass.conf

The content follows the form server.example.ac.uk:5432:researchdb:my_research:password. Also, curiously, I had to set

Sys.setenv(PGPASSFILE = "C:/Users/paul/AppData/Roaming/postgresql/pgpass.conf")

My machine is locked down, so I couldn’t test this with psql -d official -h etc., but I store connection details (without the password) in a .env file:

PGHOST=server.example.ac.uk
PGPORT=5432
PGDATABASE=researchdb
PGUSER=my_research

In R:

library(dotenv)
load_dot_env()

(or to be honest, just dotenv::load_dot_env() is enough to make these available as environment variables. That means I can do the whole

Sys.getenv("PGHOST")
Sys.getenv("PGDATABASE")

etc. when creating database connections.

This keeps passwords in .pgpass and connection metadata in .env.

Database Transfer Workflow

I’ve been tearing my hair out with this. I use sqitch to manage the database, and a makefile which co-ordinates the bash or R scripts that import data into the staging schema and then call the promotion functions that move these data into the database. However, I really didn’t have the energy to figure out how to reimplement this on a very locked down Windows PC (and there is another story about how I can’t access the VPN from Debian), so I thought I’d treat my working database as a “model”, and clone part of it. It turns out the expensive part of the workflow is the transfer, not the restore as I need to upload files to OneDrive, then they need to download from there onto a laptop before they can be scp’d (yes, that’s the only tool I have) onto the database server.

Therefore:

• validate locally first;
• only transfer once confident everything required is present.

In other words, create a mini-database locally and make sure I can run the queries I need to run on that, before committing hours to the various file transfers.

Preferred approach for transfer

1. Save schema separately

It turns out to be more effective to port the entire schema,

pg_dump --schema-only mydb > schema.sql

This file can be transferred and restored before anything else:

psql mydb < schema.sql

This guarantees that:

• all tables exist,
• all views exist,
• all functions exist,
• all sequences exist,
• all enums exist

even if data are only loaded into selected tables.

2. Save table data separately

Then dump only the required data tables:

pg_dump \
--data-only \
--no-schema \
-t table1 \
-t table2 \
-t table3 \
mydb > data.sql

It turns out I have enough tables that it’s a faff marshalling one of these for each table. I did think about excluding the large tables (energy certificates, MOTs)

pg_dump \
--exclude-table-data=big_table_1 \
--exclude-table-data=big_table_2

And I could check on table sizes

SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

3. Compress before transfer

You can use custom format with compression

pg_dump -Fc ...

However, I opted for manually compressing

tar -czf transfer.tar.gz data/

4. Smoke-test locally

Before transferring:

• Create a fresh local database.
• Restore schema.
• Restore data.
• Run representative queries.

This catches missing tables before the painful transfer process.

5. Script the process

I haven’t done this yet, but I could

#!/bin/bash
pg_dump --schema-only mydb > schema.sql
pg_dump \
--data-only \
--no-schema \
-t journeys \
-t locations \
-t weather \
mydb > data.sql

This avoids relying on memory months later.

Summary of Key Improvements

• Use a dedicated read-only user (me_research).
• Store passwords in .pgpass / pgpass.conf.
• Store connection parameters in .env.
• chmod 600 ~/.pgpass on Linux.
• Load environment variables with dotenv::load_dot_env().
• Dump schema separately (--schema-only).
• Dump data separately (--data-only --no-schema).
• Maintain a scripted list of included/excluded tables.
• Compress before transfer.
• Restore and smoke-test locally before uploading to the server.