Backup and Restore Postgres Using pg_dump and psql

This is how you backup your Postgres Database

To Backup

pg_dump --host=localhost --port=5432 --username=postgres --file=YOUR_FILENAME.sql YOUR_DB_NAME

There are other options such as if you’re interested only on the structure then you can add:

pg_dump --host=localhost --port=5432 --username=postgres --schema-only --format=p --create --inserts --file=YOUR_FILENAME.sql YOUR_DB_NAME

To Restore

psql -Upostgres -hlocalhost YOUR_DB_NAME < YOUR_FILENAME.sql

Integer Array Casting in PostgreSQL


-- Rule: "_DELETE" ON sometable
-- DROP RULE "_DELETE" ON sometable;

CREATE OR REPLACE RULE "_DELETE" AS
ON DELETE TO sometable DO INSTEAD DELETE FROM _sometable
WHERE _sometable.account_id = old.account_id AND (old.domain_id = ANY ((( SELECT get_visible_domains('DELETE'::text) AS get_visible_domains)::integer[])));