PSQL Stuf

• Useful Postgresql Notes/Cheatsheet

Connect to remote postgres with SSH tunneling:

ssh -L 5433:localhost:5432 user@host.com

Then in another terminal:

psql -h localhost -p 5433 -U symfony -d newdatabase

Add Read only Postgres user:

Add like this:

CREATE ROLE symfony_read_only WITH LOGIN PASSWORD 'autogeneratedpassword'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
REVOKE ALL ON DATABASE symfony_app FROM symfony_read_only;
GRANT CONNECT ON DATABASE symfony_app TO symfony_read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO symfony_read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO symfony_read_only;

Create DB Dump:

 pg_dump wordpress_db > /tmp/wordpress_db.dump

Restore:

psql wordpress_db < /tmp/wordpress_db.dump