Backup/Restore

# Backup
pg_dump --create DATABASE_NAME | gzip > DATABASE_NAME.sql.gz

# Restore
gunzip --stdout DATABASE_NAME.sql.gz | psql --echo-all

Database operations

# Create postgres database for user
sudo -u <linux_username> createdb
# List databases
=> \list

# Drop database
=> DROP DATABASE <DATABASE_NAME>;

# Create User
=> CREATE USER <USER_NAME> CREATEDB;

###

How to query JSONB

SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; 

SELECT * FROM users WHERE metadata->>'country' = 'Peru';

SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;

SELECT * FROM users 
  WHERE metadata->>'country' IN ('Chad', 'Japan');
  
SELECT * FROM users
  WHERE metadata->'address'->>'city' = 'Melbourne';

SELECT metadata->>'country' FROM users;
  • -> returns object.
  • ->> coeaces into value.

How to reset sequence

Default sequence name format seems to be <table>_<column>_seq.

ALTER SEQUENCE events_serial_id_seq RESTART WITH 35730728;

Log full SQL statements in postgresql log

Edit postgres/10.5/data/postgresql.conf, add:

log_statement = 'all'			# none, ddl, mod, all