Restore database from backup

Assuming backups done by automypgsqlbackup.sh.

• Grab desired backup file from obsuser01 and unzip ⇒ e.g., ispms_2017-11-13_01h30m.Monday.sql.
• sudo su postgres
• psql --set ON_ERROR_STOP=on scratch < ispms_2017-11-13_01h30m.Monday.sql (database name is irrelevant, as long as it exists)

Show number of connections

select
    max_conn,used,
    res_for_super,
    max_conn - used - res_for_super as res_for_normal 
from
    (select count(*) as used from pg_stat_activity) as t1,
    (select setting::int as res_for_super from pg_settings where name=$$superuser_reserved_connections$$) as t2,
    (select setting::int as max_conn from pg_settings where name=$$max_connections$$) as t3;

(https://dba.stackexchange.com/a/161761)

Database breakage

If you get something like this when attempting to start up the postmaster:

LOG:  startup process (PID 14231) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was shut down at 2006-08-04 17:38:43 NZST
LOG:  record with zero length at 0/AC613AC
LOG:  invalid primary checkpoint record
LOG:  record with zero length at 0/AC6136C
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 14255) was terminated by signal 6
LOG:  aborting startup due to startup process failure

then make a backup of the existing data directory ($DATA_DIR) and try this:

sudo -u postgres /PATH/TO/pg_resetxlog $DATA_DIR

Substitute appropriate paths, versions, etc. It might just fix it, and you’re no worse off if it doesn’t ☺.