Postrgres Notes

hba config

sudo nano /etc/postgresql/11/main/pg_hba.conf
> host    all    all    x.x.x.x/xx    md5
psql -U postgres
> SELECT pg_reload_conf();

Create User

su postgres
createuser --interactive --pwprompt

psql -U postgres
GRANT ALL ON DATABASE <database> TO <user>;
\c <database>
GRANT USAGE ON SCHEMA public TO <user>;
GRANT ALL ON ALL TABLES IN SCHEMA public TO <user>;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO <user>;

SELECT tablename, tableowner FROM pg_tables WHERE tableowner = '<old_user>';
REASSIGN OWNED BY <database> TO <new_user>;
SELECT tablename, tableowner FROM pg_tables WHERE tableowner = '<new_user>';

For privileges See: https://www.postgresql.org/docs/11/sql-grant.html

COPY

Dump whole table to CSV

COPY users TO '/tmp/users_table.csv' DELIMITER ',' CSV;

Dump selected columns to CSV

COPY users(username,email) TO '/tmp/users_table.csv' DELIMITER ',' CSV;

Load from CSV

COPY users FROM '/tmp/users_table.csv' WITH CSV;

Load selected columns from CSV

COPY users(username,email) FROM '/tmp/users_table.csv' WITH CSV;

Note: If connecting remotely you can write the file to the local file system (rather than the server) by prefixing the command with \.

\COPY users TO '/tmp/users_table.csv' DELIMITER ',' CSV;