Nandling

Move data

Make a database, then move tables in and out of that database. This site does not import or export. Postgres does. The host, user, and password are on the database page. The connection is TLS.

Bring a table in

From the database you have now. TABLE is one table. Leave off --table to take that whole database.

pg_dump "host=OLD port=5432 user=OLDUSER dbname=OLDDB sslmode=require" --table=TABLE --no-owner --no-privileges -f table.sql

Into your Nandling database. NAME and USER are the ones on the database page. Postgres asks for that password.

psql "host=NAME port=5432 user=USER dbname=USER sslmode=require" -f table.sql

From Amazon RDS, Aurora, or Cloud SQL

Take care if the old host is Amazon RDS for PostgreSQL, Aurora PostgreSQL, or Google Cloud SQL for PostgreSQL. Those services do not give you a superuser. pg_dumpall needs that, and it needs pg_authid for role passwords. Without them the dump is incomplete, or it fails. Dump each application database with pg_dump, as above. Recreate users and passwords here. Do not load a cluster dump that tries to create tablespaces, managed roles, or server settings from that host.

Amazon RDS and Aurora: the master user is rds_superuser, not superuser. It cannot read pg_authid, so pg_dumpall only runs with --no-role-passwords. You will be missing:

  • Role passwords. Set them again after the users exist.
  • The managed rdsadmin database. You cannot connect to it, and it is not yours to move.
  • Superuser on roles. That attribute does not leave RDS.
  • Tablespace files. RDS tablespaces sit on one volume under /rdsdbdata. Those paths will not exist here.
  • Parameter groups and other instance settings. Those are not in a dump.

Google Cloud SQL: the same caution. cloudsqlsuperuser is not superuser. pg_dumpall does not have access to cloudsqladmin or the template databases, so those must be excluded. A globals dump needs superuser, which Cloud SQL does not give you. Role passwords are missing unless you set cloudsql.pg_authid_select_role. If the instance has several databases with several owners, pg_dumpall can fail; dump each database with pg_dump instead. You will be missing:

  • Role passwords, unless that flag was set. Set them again here either way.
  • The managed cloudsqladmin database, and the template databases.
  • Superuser on roles.
  • A globals-only dump of roles and tablespaces. That path requires superuser.
  • Instance flags and other host settings. Those are not in a dump.

Amazon on pg_dumpall and pg_authid. Google on pg_dumpall and cloudsqladmin.

Take this database out

pg_dump writes this database. Leave off --table for the whole database. --no-owner leaves ownership out of the file.

pg_dump "host=NAME port=5432 user=USER dbname=USER sslmode=require" --no-owner --no-privileges -f nandling.sql

Load that file into a database you run, with your own user.

psql "host=YOURS port=5432 user=YOURUSER dbname=YOURDB sslmode=require" -f nandling.sql

Snapshots

A snapshot is not this file. It stays on the backup server, encrypted, and it can be loaded back into a database on the same account. It cannot be downloaded, and a file from somewhere else cannot be uploaded as one. The quick start describes how many are kept and how the keys work.