PITR recovery fails when there are no transactions (pgbackrest)

Prolem

You are trying to do PITR recovery for a specific time, but the restore fails with something similar to the following message

FATAL:  recovery ended before configured recovery target was reached

Solution

This seems to be caused by a feature/bug that causes this error when there are no transactions between the backups, as explained in more detail here

PostgreSQL upgrading from 9.6 to 15.3 has authentication failed (PG::ConnectionBad)

After upgrading a PostgreSQL instance from version 9.6 to the currently latest version 15.3, your application cannot connect to the server, giving an authentication error even if the user/password used is still the same.

This seems to be the result of a change in the method used in the pg_hba.conf file.

Version 9.6 uses md5 but version 15.3 uses scram-sha-256

Changing this in the file pg_hba.conf file from scram-sha-256 to md5 and reloading the configuration file from inside the database with select pg_reload_conf(); , results in the application being able to connect to the database server again.

Update: It is possible to use the recommended scram-sha-256 hash, by doing the following:

  • Uncomment the line with #password_encryption = scram-sha-256 # scram-sha-256 or md5 in file /var/lib/postgresql/data/pgdata/postgresql.conf
  • reload the configuration in the database select pg_reload_conf();
  • recreate the password with the new encryption production=# alter user user_name with password 'password';

PostgreSQL connection string for Percona PostgreSQL K8S operator

Since the documentation does not contain any information about how you can connect an existing application to the newly created percona pgo cluster, you can use something like the following in your pod postgresql connection string.

postgresql://username:password@cluster1.pgo-perc-production.svc.cluster.local/production

where cluster1.pgo-perc-production.svc.cluster.local points to your newly created cluster and the /production is the database to connect to.

Stopping a blocking query in postgresql

Problem

You run a query in PostgreSQL that takes a lot of time to finish (maybe because of an error) and you want to stop/kill it as it is blocking other queries in db.

Solution

You will need to find the pid for the query, either by using a GUI program like Pgadmin or by issuing the following to an SQL prompt:

select * from pg_stat_activity

make a note of the pid of the query that you want to stop/kill and issue the following:

select pg_cancel_backend(pid)

function for getting the bitmask integer value in PostgreSQL

Problem

You have set up some bitmask values and you would like to return the calculated value given a string with one or more of the codes used, to be able to used it in an SQL query.

Solution

You can use the following to create a function called bitmask. This one deals with any number of individual codes (2 and 3 digit codes in this case).
It also does not calculate duplicate values since they are removed.
So for this example the string ’02a’ is the same as ’02a,02a’ and should give 16 in both cases.

CREATE OR REPLACE FUNCTION bitmask(code text) RETURNS integer AS $$
  DECLARE bm INTEGER := 0;
  DECLARE total INTEGER := 0;
  DECLARE counter INTEGER := 0;
  DECLARE code_to_translate TEXT := '';

  BEGIN
    LOOP 
      EXIT WHEN LENGTH(code) = 0;
      /* only take first 2 or 3 characters */
      code_to_translate := SPLIT_PART(code, ',', 1);
      CASE code_to_translate
        WHEN 'XX'  THEN bm =      0;
        WHEN '01a' THEN bm =      2;
        WHEN '01b' THEN bm =      4;
        WHEN '01c' THEN bm =      8;
        WHEN '02a' THEN bm =     16;
        WHEN '02b' THEN bm =     32;
        WHEN '03'  THEN bm =     64;
        WHEN '04a' THEN bm =    128;
        WHEN '04b' THEN bm =    256;
        WHEN '04c' THEN bm =    512;
        WHEN '04d' THEN bm =   1024;
        WHEN '05'  THEN bm =   2048;
        WHEN '06a' THEN bm =   4096;
        WHEN '06b' THEN bm =   8192;
        WHEN '10a' THEN bm =  16384;
        WHEN '10b' THEN bm =  32768;
        WHEN '10c' THEN bm =  65536;
        WHEN '10d' THEN bm = 131072;
        WHEN '11a' THEN bm = 262144;
        WHEN '11b' THEN bm = 524288;
        ELSE bm = 0;
      END CASE;
      total := total + bm;
      counter := counter + 1;
      /* Remove checked code so we do not have duplicates */
      code := regexp_replace(code, code_to_translate||'[,]*', '', 'g');
    END LOOP ;
   RETURN total;
  END;
$$ LANGUAGE plpgsql;

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: …

Problem

You have a Ruby on Rails application/engine with some tests in Minitest and using postgresql as the db. In the original development environment all the tests that use fixtures and foreign key validations pass, but when you try to move to a different environment with new database some or all of you tests fail with a message that refers to the ForeignKeyViolation as in:

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  insert or update on table "tbl_name" violates foreign key constraint "tbl_name_cln_name_fk_tbl_name_fk"
DETAIL:  Key (col_name)=(VALUE) is not present in table "fk_tbl_name".

Solution

Since active record disables the foreign keys before loading the fixtures in the database, the user that you run the tests with needs to have superuser permissions in the test database. Otherwise the fixtures are not loaded. So add the superuser permissions to your user:

psql -U super_user
alter role use_for_tests superuser;

You may need to drop and create your test database.