postgre.md

PostgresSQL Cheatsheet

1. Backup and restore with CLI

   sudo apt-get install postgresql-client
   
   # backup
   PGPASSWORD=$PASSWORD pg_dump -U $USERNAME -h $URL -p $PORT -n $SCHEMA $DATABASE > dump-file.sql
   
   # restore
   PGPASSWORD=$PASSWORD psql -U $USERNAME -h $URL -p $PORT $DATABASE < dump-file.sql

2. Access the database via CLI

   PGPASSWORD=$PASSWORD psql -U $USERNAME -h $URL -p $PORT $DATABASE

3. foreign keys are not indexed per se -- they are only indexed on the external table as primary keys. So don't forget to index them manually

4. Best timestamp format: timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP

5. Postgres has enums:

   CREATE TYPE partyws.some_enum AS ENUM ('FOO', 'BAR');
   

6. Debug query performance with EXPLAIN ANALYZE ...

7. Mock DB for tests:

   npm i -D @shelfio/jest-postgres