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
- reload the configuration in the database
- recreate the password with the new encryption
production=# alter user user_name with password 'password';
To display the results of a postgresql query as a table (in the example postgresql runs as a docker container locally) use something like the following
psql -h localhost -p 5555 -U postgres database_name -xc 'select * from table_name where id=29'
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.
where cluster1.pgo-perc-production.svc.cluster.local points to your newly created cluster and the /production is the database to connect to.
There are different ways of using the json(b) operators in a PostgreSQL column.
The differences between two of them (->) and (->>) are described below:
‘[1,2,3]’::json->2 returns “2”
‘[1,2,3]’::json->>2 returns 2
While issuing the query TRUNCATE table TABLE_NAME works for MySQL for restarting the id of the table, this does not work with PostgreSQL.
In order to restart the sequence of the table in PostgreSQL you will have to issue the following:
TRUNCATE TABLE table_name RESTART IDENTITY;
You need to set up postgres for the first time in a new computer and you need the instructions in one place.
Follow the example given in the answer below:
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.
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:
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.
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 := '';
EXIT WHEN LENGTH(code) = 0;
/* only take first 2 or 3 characters */
code_to_translate := SPLIT_PART(code, ',', 1);
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;
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 ;
$$ LANGUAGE plpgsql;
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".
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.
You want to use the PL/R extension in PostgreSQL, and after the package installation you need to set up the R_HOME in PostgresQL.
Add the following line to the bottom of your PostgreSQL configuration file as sudo:
sudo vi /etc/postgresql/9.3/main/postgresql.conf