Postgres

1 Initial setup after postgres installation

https://stackoverflow.com/a/26735105/8762905

  1. After installing Postgresql, open the file pg_hba.conf for Ubuntu it will be in /etc/postgresql/9.x/main and change this line:

    local   all             postgres                                peer

    to

    local   all             postgres                                trust

  2. Restart the server

    $ sudo service postgresql restart

  3. Login into psql and set your password

    $ psql -U postgres
postgres=# ALTER USER postgres with password 'your-pass';

  4. Finally change the pg_hba.conf from

    local   all             postgres                                trust

    to

    local   all             postgres                                md5

  5. After restarting the postgresql server, you can access it with your own password

    Authentication methods details:

    trust - anyone who can connect to the server is authorized to access the database

    peer - use client’s operating system user name as database user name to access it.

    md5 - password-base authentication

2 Create user/role and database

  1. connect to postgres

    sudo -u postgres psql

  2. create role and database

    CREATE USER testuser WITH PASSWORD 'testuser';
CREATE DATABASE testdb ENCODING 'UTF8' OWNER testuser;
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

3 Enable uuid

\connect testdb

CREATE EXTENSION "uuid-ossp";

4 Query date field

SELECT *
FROM table
WHERE update_date >= '2013-05-03'::date
AND update_date < ('2013-05-03'::date + '1 day'::interval);

5 PostgreSQL: alter ALL tables OWNER TO myuser

Get the list of tables

SELECT table_name from information_schema.tables WHERE table_schema = 'public';

Create SQL for alter table owner

select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';

Update table owner

psql -qAt -c "select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;'
from information_schema.tables where table_schema = 'public'" | psql

Check table owner

select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

6 Debugging postgres database issues

When debugging postgres issues, in addition to the standard pg_top tool, often it can be useful to use this query:

SELECT procpid,waiting,query_start,current_query FROM pg_stat_activity ORDER BY procpid;

which shows the currently running backends and their activity. This is similar to the pg_top output, with the added advantage of showing the complete query, which can be valuable in debugging.

To stop a runaway query, you can run SELECT pg_cancel_backend(pid int) or SELECT pg_terminate_backend(pid int) as the ‘postgres’ user. The former cancels the backend’s current query and the latter terminates the backend process. They are implemented by sending SIGINT and SIGTERM to the processes, respectively. We recommend against sending a Postgres process SIGKILL. Doing so will cause the database to kill all current connections, roll back any pending transactions, and enter recovery mode.

7 Find rows in all tables

SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

8 postgres mac osx directory

/usr/local/var/postgres /usr/local/var/log


© 2015-2020 tendant