Usage

  • backup && restore
1
2

$ pg_dump dbname > outfile
$ psql dbname < infile
  • create database with owner
1
2
3
4
5

$ sudo su - postgres
$ psql
> CREATE USER who-am-i WITH PASSWORD 'change-me';
> CREATE DATABASE db-name WITH ENCODING='UTF8';
> GRANT ALL PRIVILEGES ON DATABASE db-name TO who-am-i;
  • apply privileges to user
1
2
3
4

> \c db-name
> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC TO who-am-i;
> GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA PUBLIC TO who-am-i;
> GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA PUBLIC TO who-am-i;
  • psql
1
2
3
4
5

> psql -U USER -d DB -W
> \l # show databases
> \c db-name # connect database
> \d # show tables
> \d table-name # show table scheme
  • change owner
1

> ALTER DATABASE db-name OWNER TO who-am-i;

1
2
3
4
5
6
7
8
9

> SELECT format(
  'ALTER TABLE %I.%I.%I OWNER TO %I;',
  table_catalog,
  table_schema,
  table_name,
  'who-am-i'
)
FROM information_schema.tables
WHERE table_schema = 'db-name';

In psql, you can run them by following it immediately with \gexec

Issues

  • ‘Peer authentication failed for user’, open file “pg_hba.conf” change line:
1

local   all             all             192.168.0.0/24                        md5