Usual, frequent backups – containing just about everything – are highly favoured by a wide-range of sysadmins. In some scenarios, it is more than enough to just issue a full backup (including the kitchen sink) then to make selective and separate dumps from parts of the database, based on the size and incremency of the database.
When restoring a dump made with pg_dumpall to a freshly built host, the following – most uncomfortable – situation had risen:
– authentication failed for logging in with one of the users
– remote access via pgAdmin III was denied
To remedy the situation, the following steps were made:
1. Change the accepted addresses of the PostgreSQL server
# nano /etc/postgresql/8.4/main/pg_hba.conf
>> add this line:
host all all [your_ip_address_pool]/24 password
2. Change the listen address(es) of the PostgreSQL server
# nano /etc/postgresql/8.4/main/postgresql.conf
>> find and change this line:
listen_addresses=’localhost’
>> to this:
listen_addresses=”[the_ip_you_want_to_bind_it_to]”
3. Alternatively, change the (re-)password of the login user
# sudo postgres psql
postgres=# ALTER USER dbuser WITH PASSWORD ‘password’;
>> also let’s re-enable the login feature
postgres=# ALTER USER dbuser WITH LOGIN;