PostgreSQL: How to reload config settings without restarting database

Source

If you are making modifications to the Postgres configuration file postgresql.conf (or similar), and you want to new settings to take effect without needing to restart the entire database, there are two ways to accomplish this.

Option 1: From the command-line shell

su - postgres
/usr/bin/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();
Using either option will not interrupt any active queries or connections to the database, thus applying these changes seemlessly.

Create a read only account

Script to Create Read-Only user:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

How to drop a PostgreSQL database if there are active connections to it?

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

Openfire. Howto get message history from database

1) PostgreSQL

 SELECT
'1970-01-01 00:00:00 GMT'::timestamp with time zone + ((ofmessagearchive.sentdate/1000)::text)::interval AS time,
ofmessagearchive.fromjid AS from,
ofmessagearchive.tojid AS to,
ofmessagearchive.body AS message
FROM
public.ofmessagearchive
WHERE
( ofmessagearchive.fromjid = '<JID of user>'
OR
ofmessagearchive.tojid = '<JID of user>' )
AND
ofmessagearchive.sentdate > EXTRACT(EPOCH FROM timestamp with time zone 'start time in format <YYYY-MM-DD HH-MM-SS>') * 1000
AND
ofmessagearchive.sentdate < EXTRACT(EPOCH FROM timestamp with time zone 'end time in format <YYYY-MM-DD HH-MM-SS>') * 1000
ORDER BY 1;

Modify OWNER on all tables simultaneously in PostgreSQL Database

1) pg_dump -s YOUR_DB | grep -i 'owner to' | \

sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | \

psqL YOUR_DB

2) 

Tables
SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO my_new_owner;' FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;


Sequences

SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

Views
SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO my_new_owner;' FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;


This generates all the required ALTER TABLE / ALTER SEQUENCE / ALTER VIEW statements, copy these and paste them back into plsql to run them.
Check your work in psql by doing:

\dt *.*
\ds *.*
\dv *.*