Connecting to PSQL

Connection string

postgres://YourUserName:YourPassword@YourHostname:5432/YourDatabaseName

pg_dump

pg_dump -d postgres://postgres_connection_string --no-owner --no-acl -Fc -f dump_name.dump

• -Fc custom format (aka zipped)
• -f file name
• -T excludes a table, there can be multiple
• -t only include this table, there can be multiple

pg_restore

pg_restore -d postgres://postgres_connection_string --no-owner --no-acl dump_name.dump

• -d also accepts a local db name
• --no-owner removes the ownership
• --no-acl removes access privileges (grant/revoke commands)
• --section=pre-data,data,post-data
  • pre-data: table/schema def, sequences, owner,
  • post-data: (foreign) constraints

Copy a table in the same db

CREATE TABLE new_table AS TABLE existing_table;

Copy table to another database

pg_dump --no-owner --no-acl -t table_to_copy source_db | psql target_db

• dumps as a .sql file so you can just run with psql

Cloud access

AWS RDS on a VPC via SSM

1. Create an ssh tunnel from your machine to an EC2 container in the VPC

aws ssm start-session --target "container-id-ec2, like i-0373fb85e5fbc7d8e" --document-name AWS-StartPortForwardingSession --parameters '{"portNumber":["22"],"localPortNumber":["56789"]}'

1. Connect to the

   • ssh -p 56789 root@localhost

2. note the user should be root or whatever is expected on the EC2 machine

3. not your personal username

4. ssh root@127.0.0.1 -p 56789 -N -L 5433:{prod db url}:5432

---

Last update: 2023-04-24