tirdadc

Web Development | Ruby on Rails | React.js

Postgres Reminder

Finally moving this out of notes.txt into the blog for future reference.

To kill a connection:

SELECT pid FROM pg_stat_activity where pid <> pg_backend_pid();
SELECT pg_terminate_backend($1);

To dump a database into a SQL-script file:

pg_dump db_name > outfile.sql

To dump a database into a compressed file:

pg_dump -Fc db_name > outfile.dump

To restore a database:

psql db_name < infile.sql

To grant all privileges to a user:

GRANT ALL PRIVILEGES ON DATABASE db_name TO db_user;

log in as admin :

psql -U postgres

change owner of database:

ALTER DATABASE name OWNER TO new_owner;

Change a user’s password:

ALTER USER "user_name" WITH PASSWORD 'new_password';

To view all users:

\du

To connect to a database as a specific user:

psql -d database_name -U user

To change a database’s owner:

ALTER DATABASE name OWNER TO new_owner;