Collectd PostgreSQL Plugin

I couldn't find this link when searching with google

https://www.collectd.org/documentation/manpages/collectd.conf.html#plugin-postgresql

[PostgreSQL] How to check null in jsonb array

 Use jsonb_array_elements  to convert jsonb array to set (queriable similar to normal table) then we can use  where x::text = 'null' to match the null value.

Examples:

=> select x->'id' as id 
from jsonb_array_elements(
'[{"id": 4, "val": [null,1,null]}, 
{"id": 2, "val": [3]}]'::jsonb
) as x, 
jsonb_array_elements(x->'val') as y 
where y::text = 'null';
 id 
----
 4
 4
(2 rows)


=> select x->'id' as id
from jsonb_array_elements(
'[{"id": 4, "val": [null,1,null]}, 
{"id": 2, "val": [3]}]'::jsonb
) as x, 
jsonb_array_elements(x->'val') as y 
group by x->'id' 
having count(x) filter (where y::text  = 'null') > 1;
 id 
----------
 4
(1 row)

Adapted from: https://dba.stackexchange.com/a/257799

[PostgreSQL] How to check if there's a NULL value in array

WHERE array_position(array_column, NULL) IS NOT NULL 

Credit: https://stackoverflow.com/a/47984368/1032449

[PostgreSQL] How to create read-only standby replica (follower)

This link and this link explain it really well and concise

[PostgreSQL] How to verify if replication catch up with master change

[Run on master] Check master location with

SELECT pg_current_xlog_location();

 [Run on replica] Check replica replayed location with

SELECT pg_last_xlog_replay_location();

[Run on master] Check replica status from master

SELECT client_addr, state, sent_location, write_location, flush_location, replay_location FROM pg_stat_replication;

*write, flush, replay are status of replica

[Run on replica] Check time from the latest reply

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Credit:

• Bullet 12th of https://wiki.postgresql.org/wiki/Streaming_Replication
• https://www.niwi.nz/2013/02/16/replication-status-in-postgresql/

[PostgreSQL] List index usage

Update: This query is much cooler Index size/usage statistics

--

In psql console, connect to the database you want to check its indexes usage. Then execute this query.

# SELECT * FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

If your database is manage by Rails's schema, you could filter out auto generated public key indexes like so

# SELECT * FROM pg_stat_user_indexes WHERE indexrelname NOT LIKE '%_pkey' ORDER BY idx_scan DESC; 

[PostgreSQL] Using some indexes from Composite index

If we had a composite index on three fields: we would be able to use it for queries on the first field, for queries on the first two fields and for queries involving all three fields, but not on any other field combination.

Copied directly from How to stop worrying and love your Postgres indexes 

[PostgreSQL] List running queries

In psql, run this command

select * from pg_stat_activity where datname = YOUR_DB_NAME and state = 'active'

 You might also what to order the result by query_start or state_change

[PostgreSQL] See SQL queries (without restarting the server!)

1. In postgresql.conf (probably under /etc/postgres/...), uncomment log_statement and set to 'all'

log_statement = 'all'

2. To reload config, sending SIGHUP to postgres server by going to psql shell and execute:

select pg_reload_conf();

    There's an another way to send SIGHUP, please refer to the link below

3. Observe the queries at postgresql.log (probably under /var/log/...)
4. To disable, comment out log_statement and reload config again

log_statement, reloading postgresql config

Credit: http://stackoverflow.com/a/8208376

Clone a PostgreSQL database

At some point in development, you may want to make a copy of your existing development database to play with an unstable new feature. Postgres makes it super trivial to do that by allow us to create a new database by using existing database as a template.

In postgres console, run

=# CREATE DATABASE new_database_name WITH TEMPLATE original_database_name;

Boom!, you got a cloned database named new_database_name.

Credit: Creating a copy of a database in Postgres
PS: I've seen a convenient way to make a clone across remote server too. (Haven't try) How to copy postgres database to another server

Update Aug 27, 2015:
Instead of running command in psql, we can use createdb with -T flag like so

$ createdb -T original_database_name new_database_name

Credit: http://stackoverflow.com/a/6739995 

pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery

I ran to this error the other day when I tried to export a dump file from a slave Postgres database.

cpg_dump: Dumping the contents of table "[table_name]" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
pg_dump: The command was: COPY public.[table_name] ([comma-separated column names]) TO stdout;

I turns out that we can't execute long query on Hot Standby mode server. Right now, there's no perfect solution to this problem but there are some couples of workarounds.

• Make a dump from master.
• Set max_standby_archive_delay and max_standby_streaming_delay to be bigger that amount of time it needs to export a dump. This will make slave catching up master version slower. It's not likely to work for a big database.
• Pause sync, make a dump and the resume sync. You can pause sync with function pg_xlog_replay_pause() and resume with function pg_xlog_replay_resume(). See more details on this 2 functions from 9.24. System Administration Functions and New Replication and Recovery Features in PostgreSQL 9.1. Note that we should not pause it too long (> 24 hours), otherwise I might not be able to resume.

Mailing list related to this issue: Hot Standby - ERROR: canceling statement due to conflict with recovery

PostgreSQL: Listen to remote connection

By default postgres does not allow accessing via ip address, you can only access with localhost. If you try to access with ip address, you will get the error message like this: (assume that 10.0.1.11 = localhost)

$ psql -h 10.0.1.11 dbname
psql: could not connect to server: Connection refused
Is the server running on host "10.0.1.11" and accepting
TCP/IP connections on port 5432?

To allow connection via ip address, you need to add that ip address to listen_addresses config. Here's how to do that:

1. Open file postgresql.conf (the location of the file depend on OS and installed place)
2. Look for listen_addresses
3. Uncomment the line
4. Add ipaddress that you want postgres to listen on inside the same single quote with 'localhost'. Like this listen_addresses = 'localhost,10.0.1.11'
5. Restart postgres server (again depend on your OS on how to restart service)
6. Now, you should be able to access it with "-h ipaddress". Such as psql -h 10.0.1.11 dbname

PostgreSQL - export/import schema only

Export

Use -s to specify schema only

pg_dump <src_database_name> -s > schema.sql

(Optional) Use -t to specify table. For more than 1 table just add more -t.

 pg_dump <src_database_name> -s -t <table1_name> -t <table2_name> > schema.sql

Import

psql <dest_database_name> -f schema.sql

Credit:

• How to Import / Export in Postgres Database ?
• How to import existing *.sql files in Postgresql 8.4?
• pg_dump

[PostgreSQL][Mac] Modify root password (Forgot password)

• sudo su postgres
• supply your Mac password
• modify /Library/PostgreSQL/9.x/data/pg_hba.conf
• from local all all   md5
• to local all all   trust
• execute /Library/PostgreSQL/9.x/bin/pg_ctl restart -D /Library/PostgreSQL/9.x/data/
• run psql
• change postgres password
• ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';
• modify pg_hba.conf back
• restart again

Tested with PostgreSQL version 9.1 OSX Lion