Troubleshooting
Permission problems
If you're receiving errors like ERROR: permission denied for table x, check that the permissions and owners are as you expect them.
PostgreSQL does have separate permissions and owners for each object (e.g. database, schema, table). Being the owner of the database only implies permissions to create objects in it, but does not grant any permissions on object in the database which are created by other users. For example, selecting data from a table in the database is permitted only when the user is the owner of the table or has been granted read privileges to it.
To show the owners and access privileges you can use this command. What each letter in access privileges stands for is documented in https://www.postgresql.org/docs/13/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE
Include the output of this command if you open a support ticket related to permission problems.
Network issues
If you see error messages like psql: error: could not connect to server: ..., you can try to find the specific problem by executing these commands (on the client machine having the problems, assuming Linux):
To show local IP adresses:
Make sure that the IP address of the database cluster is NOT listed here. Otherwise this means that the IP address of the cluster collides with your local machines IP address. Make sure to select a non-DHCP IP address for the database cluster (between x.x.x.2/24 and x.x.x.10/24).
To list the known network neighbors:
Make sure that the IP address of the database cluster shows up here and is not FAILED. If it is missing: make sure that the database cluster is connected to the correct LAN in the correct datacenter.
Test that the database cluster IP is reachable:
This should show no package loss and rtt times should be around some milliseconds (may depend on your network setup).
To finally test the connection using the PostgreSQL protocol:
Some possible error messages are:
No route to host: Can't connect on layer 3 (IP). Maybe incorrect LAN connection.Connection refused: Can reach the target, but it refuses to answer on this port. Could be that IP address is also used by another machine that has no PostgreSQL running.password authentication failed for user "x": The password is incorrect.
If you're opening a support ticket, attach the output of the check-net-config.sh script, the output of psql -h $ip -U $user -d postgres and the command showing your problem.
Issues with backup restore
Under some circumstances, in-place restore might fail. This is because some SQL statements are not transactional (most notably DROP DATABASE). A typical use case for in-place restore arises after the deletion of a database.
If a database is dropped, first, the data is removed from disk and then the database is removed from pg_database. These two changes are not transactional. In this event, you will want to revert this change by restoring to a time before the drop was issued. Internally, Postgres replays all transactions until a transaction commits after the specified recovery target time. At this point all uncommitted transactions are aborted. However, the deletion of the database from disk cannot be inverted. As a result, the database is still listed in pg_database but trying to connect to it results in the following:
DBaaS will perform some initialization on start-up. At this point the database will go into an error loop. To restore a database to a working state again, you can request another in-place restore with an earlier target time, such that at least one transaction is between recovery target time and the drop statement. The problem was previously discussed in the Postgres mailing list here.
Last updated