Have you ever been stuck in a situation where operations like autovacuum and pg_dump exit with errors Operation not permitted , although the files have the required permissions?
Sometimes, the logs may indicate errors similar to the following:
pg_dump: archiver (db) connection to database "postgres" failed: FATAL: could not open file "global/XXXX": Operation not permitted
DETAIL: Cold not open file "pg_multixact/offsets/XXXX": Operation not permitted
While these errors are accurate, in certain situations, they may be mislead DBAs and sysadmins into thinking that either the concerned file is corrupted or doesn’t have required OS permissions.
If these error messages appear in the database server, and the permissions appear to be set correctly, an additional recommendation would be to check if any anti-virus software is running on the problematic machine, and whether it is scanning the concerned file when an operations like pg_dump and autovacuum is accessing it.
Antivirus software constantly scans for files, and Postgres can easily get into a problem because Postgres regularly creates heap, index, and WAL files, and binary content is written into these files. Depending on what data is written, these files can contain patterns that are mistaken for viruses, causing the files to be removed from Postgres-controlled directories. Obviously, this hampers the PostgreSQL database operations.
Also, while PostgreSQL is continuously reading from and writing to the same set of files from multiple processes, antivirus software has the potential to intercept relevant system calls, thereby subject data to race conditions and erratic behavior. Such phenomena can cause unexpected errors to appear or even corrupt data.
To prevent this issue, always make sure that the PostgreSQL install directory and data directory are added to the anti-virus software exclusion list.
... View more
When using pg_upgrade , you may encounter the following error:
could not load library "$libdir/PostGIS-x.x”: ERROR: could not access file "$libdir/PostGIS-x.x”: No such file or directory?
We generally come across these kind of error messages when there is a version mismatch between the PostGIS version installed on the database servers (the database server version which needs to be upgraded) and the database server version requiring an upgrade.
Here's an example version of the issue:
The user is looking to upgrade the PostgreSQL database server from EPAS 9.5 (PostGIS 2.1.8) to EPAS 10 (PostGIS 2.4.1). However, the PostGIS version needs to be same on both database server versions, otherwise the following format of error message will be encountered.
could not load library "$libdir/postgis-2.1": ERROR: could not access file "$libdir/postgis-2.1": No such file or directory could not load library "$libdir/rtpostgis-2.1": ERROR: could not access file "$libdir/rtpostgis-2.1": No such file or directory
So, the prerequisite for these scenarios would be to have the same PostGIS version installed on the database servers. We checked and found that PostGIS 2.1.8 is not released for EPAS-10 and Postgis-2.4.1 is not available for EPAS-9.5.
We need to perform the upgrade using the following procedure:
First, upgrade the EPAS 9.5 database server to EPAS 9.6 as PostGIS 2.1.8 is available for both database servers.
1) Install the latest version of EPAS 9.5
2) Install PostGIS 2.1.8 on EPAS 9.5
3) Install the latest version of EPAS 9.6
4) Install PostGIS 2.1.8 on EPAS 9.6
5) Upgrade the database server to EPAS 9.6
Next, we need to upgrade EPAS 9.6 to EPAS 10 as PostGIS 2.3.1 is available for EPAS 9.6 and PostGIS 2.3.4 is available for EPAS10.
We cannot use pg_upgrade as the PostGIS version are not compatible, so the only option left is a dump and restore method for postGIS database servers.
6) Install PostGIS 2.3.1 on EPAS 9.6:
ALTER EXTENSION postgis UPDATE TO "2.3.1";
We need to execute this as PostGIS 2.1.8 is already present on this database.
7) Use pg_dump on the database with the PostGIS extension installed.
8) Drop the databases with the PostGIS extension installed.
9) Use pg_upgrade to upgrade EPAS 9.6 to EPAS 10.
10) Start the EPAS 10 server and then install PostGIS 2.3.4
11) Create a new PostGIS database on EPAS 10
12) Restore the PostGIS database from the backup file using postgis_restore.pl
13) Verify the PostGIS database properly restored.
14) Now upgrade PostGIS 2.3.4 to PostGIS 2.4.1
ALTER EXTENSION postgis UPDATE TO "2.4.1";
... View more