Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

[Achilleas Mantzios - cloud]

hi

On 6/20/24 10:23, Dmitry O Litvintsev wrote:

Hello,

I am in the process of migrating DB to Alma9 host. The databse
is rather large - few TBs.

I have run pg_basebackup on Alma9 host and established replication from 
production to it. The idea is to quickly switch from master to this new host 
during downtime.

Establishing replication went fine. Source postgresql version is 15.6, 
destination is 15.7


You mean physical replication or logical ? In case of logical how did 
you initdb ?


Did you build postgresql from source or using a RH package ?

sorry for not being able to provide anything helpful.



When I psql into replica I get:

WARNING:  database "xxx" has a collation version mismatch
DETAIL:  The database was created using collation version 2.17, but the 
operating system provides version 2.34.
HINT:  Rebuild all objects in this database that use the default collation and 
run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL with the 
right library version.

Looking up the issue the solution seems to be

   REINDEX database xxx
   ALTER DATABASE xxx REFRESH COLLATION VERSION

But this defeats the whole idea of having short downtime because REINDEX will 
take forever.

What is this "or build PostgreSQL with the right library version"?
Is this about 15.7 vs 15.6 or is it about different glibc version between RH7 
and Alma9?

Is there a better way to handle it? I cannot afford long downtime.
This came up rather unexpectedly and I am now in a tight situation having to 
find solution fast. I do not recall having similar issue when going from RH6 to 
RH7.

Thank you for your help.

pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Achilleas Mantzios - cloud]

Hello

I notice both my kids struggling with either C or Python as first 
programming languages. I believe both are unsuitable for use as 
introductory languages to college juniors.


Python IMHO is too advanced, too rich, weird indentation rules, no 
simple for loop etc.


C, ok, punishing little kids with segmentation faults, calling by value 
VS by reference and 3ple pointers is pure sadism.


So this brings me to memory good old PASCAL from the time I was junior, 
circa 1986. PL/SQL resembles PASCAL heavily. Everything seems well 
defined, strong typing, simplicity, I think it has everything a 
programming language should have in order to be taught as an 
introductory language. But it lacks IO and file handling.


So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?

Thanks!

Re: postgresql custom variable in pg_settings table

[Achilleas Mantzios - cloud]

On 1/9/24 09:38, Yi Sun wrote:


Hello,

We custom set variable

Added patroni.nodes_count = 2 in postgresql.conf

postgres=# show patroni.nodes_count;
 patroni.nodes_count
-
 2
(1 row)

postgres=# select current_setting('patroni.nodes_count');
 current_setting
-
 2
(1 row)

But can not select it from pg_settings, as we use pgwatch2 to monitor, 
to avoid pgwatch2 script change prefer to use pg_setting not 
current_setting() function, is it possible to get the custom variable 
from pg_setting please? Thanks


You may look into pg_file_settings :

select setting from pg_file_settings  where name = 'patroni.nodes_count';



Best Regards
SY

Re: pgBackRest on old installation

[Achilleas Mantzios - cloud]

On 11/21/23 08:36, KK CHN wrote:

Thank you.  Its worked out well. But a basic doubt ? is storing the DB 
superuser password in .pgpass is advisable ? What other options do we 
have ?

#su postgres
bash-4.2$ cd

bash-4.2$ cat .pgpass
*:*:*:postgres:your_password
bash-4.2$


root has access to any file, but if you give password on the command 
line or env variable then you give this info to all users in the system.





On Mon, Nov 20, 2023 at 4:16 PM Achilleas Mantzios - cloud 
 wrote:



On 11/20/23 12:31, KK CHN wrote:

list,

I am trying pgBackRest on an RHEL 7.6 and old EDB 10 database
cluster( a legacy application.)

I have installed pgbackrest through  package install on RHEL7.6
But unable to get the basic stanza-creation working It throws an
error.


* /etc/pgbackrest.conf  as follows..*

[demo]
pg1-path=/app/edb/as10/data
pg1-port = 5444
pg1-socket-path=/tmp

[global]

repo1-cipher-pass=sUAeceWoDffSz9Q/d8sWREHe+wte3uOO9lggn5/5mTkQEempvBxQk5UbxsrDzHbw

repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
backup-user=postgres


[global:archive-push]
compress-level=3
#



[root@dbs ~]# pgbackrest version
pgBackRest 2.48
[root@dbs ~]#
#

*Postgres conf as follows... *

listen_addresses = '*'
port = 5444
unix_socket_directories = '/tmp'

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica

#


*ERROR  Getting as follows ..    What went wrong here ??*


 [root@dbs ~]# sudo -u postgres pgbackrest
--stanza=demo --log-level-console=info stanza-create
2023-11-20 21:04:05.223 P00   INFO: stanza-create command begin
2.48: --exec-id=29527-bf5e2f80 --log-level-console=info
--pg1-path=/app/edb/as10/data --pg1-port=5444
--pg1-socket-path=/tmp --repo1-cipher-pass=
--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest
--stanza=demo
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5444 host='/tmp'': connection to server
on socket "/tmp/.s.PGSQL.5444" failed: fe_sendauth: no password
supplied
ERROR: [056]: unable to find primary cluster - cannot proceed
       HINT: are all available clusters in recovery?
2023-11-20 21:04:05.224 P00   INFO: stanza-create command end:
aborted with exception [056]
[root@dbs ~]#

It complains about the password.  I followed the below tutorial
link, but no mention of password (Where to supply password, what
parameter where ?) setting here ==>
https://pgbackrest.org/user-guide-rhel.html


This is about the user connecting to the db, in general,
pgbackrest has to connect like any other app/user. So, change your
.pgpass to contain smth like the below on the top of the file :

/tmp:5444:*:postgres:your_whatever_pgsql_password

and retry




Any hints welcome..  What am I missing here ??

Best,
Krishane

Re: pgBackRest on old installation

[Achilleas Mantzios - cloud]

On 11/20/23 12:31, KK CHN wrote:

list,

I am trying pgBackRest on an RHEL 7.6 and old EDB 10 database cluster( 
a legacy application.)


I have installed pgbackrest through  package install on RHEL7.6
But unable to get the basic stanza-creation working It throws an error.


* /etc/pgbackrest.conf  as follows..*

[demo]
pg1-path=/app/edb/as10/data
pg1-port = 5444
pg1-socket-path=/tmp

[global]
repo1-cipher-pass=sUAeceWoDffSz9Q/d8sWREHe+wte3uOO9lggn5/5mTkQEempvBxQk5UbxsrDzHbw

repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
backup-user=postgres


[global:archive-push]
compress-level=3
#



[root@dbs ~]# pgbackrest version
pgBackRest 2.48
[root@dbs ~]#
#

*Postgres conf as follows... *

listen_addresses = '*'
port = 5444
unix_socket_directories = '/tmp'

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica

#


*ERROR  Getting as follows ..    What went wrong here ??*


 [root@dbs ~]# sudo -u postgres pgbackrest --stanza=demo 
--log-level-console=info stanza-create
2023-11-20 21:04:05.223 P00   INFO: stanza-create command begin 2.48: 
--exec-id=29527-bf5e2f80 --log-level-console=info 
--pg1-path=/app/edb/as10/data --pg1-port=5444 --pg1-socket-path=/tmp 
--repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc 
--repo1-path=/var/lib/pgbackrest --stanza=demo
WARN: unable to check pg1: [DbConnectError] unable to connect to 
'dbname='postgres' port=5444 host='/tmp'': connection to server on 
socket "/tmp/.s.PGSQL.5444" failed: fe_sendauth: no password supplied

ERROR: [056]: unable to find primary cluster - cannot proceed
       HINT: are all available clusters in recovery?
2023-11-20 21:04:05.224 P00   INFO: stanza-create command end: aborted 
with exception [056]

[root@dbs ~]#

It complains about the password.  I followed the below tutorial link, 
but no mention of password (Where to supply password, what parameter 
where ?) setting here ==> https://pgbackrest.org/user-guide-rhel.html


This is about the user connecting to the db, in general, pgbackrest has 
to connect like any other app/user. So, change your .pgpass to contain 
smth like the below on the top of the file :


/tmp:5444:*:postgres:your_whatever_pgsql_password

and retry




Any hints welcome..  What am I missing here ??

Best,
Krishane

PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

[Achilleas Mantzios - cloud]

Hello All

in the wiki above and specifically in this commit :

https://wiki.postgresql.org/index.php?title=Don%27t_Do_This=revision=33210=33082

someone added this section about inheritance :

"


   Don't use table inheritance

Don't use table inheritance 
. If 
you think you want to, use foreign keys instead.



 Why not?

Table inheritance was a part of a fad wherein the database was closely 
coupled to object-oriented code. It turned out that coupling things that 
closely didn't actually produce the desired results.



 When should you?

Never …almost. Now that table partitioning is done natively, that common 
use case for table inheritance has been replaced by a native feature 
that handles tuple routing, etc., without bespoke code.


One of the very few exceptions would be temporal_tables 
 extension if you are in a 
pinch and want to use that for row versioning in place of a lacking SQL 
2011 support. Table inheritance will provide a small shortcut instead of 
using |UNION ALL| to get both historical as well as current rows. Even 
then you ought to be wary of caveats 
 
while working with parent table.


"


I believe this text is false on too many accounts. So, what's the 
consensus about Inheritance in PostgreSQL, I am going to give a talk on 
it in November and I wouldn't like to advertise/promote/teach something 
that the community has decided to abandon or drop. Actually I proposed 
several topics and they chose this one (Inheritance).

Re: Presentation tools used ?

[Achilleas Mantzios - cloud]

Thank you All people!

Re: pg_basebackup / recovery

[Achilleas Mantzios - cloud]

On 4/12/23 12:32, Fabrice Chapuis wrote:

During recovery process of a self contained backup, how postgres know 
to stop reading wal when consistency is reached?



Because it knows the full packup info. It will observe the

STOP WAL LOCATION: 3BC7/4B000130 (file 00023BC7004B)

inside the backup file

 It's a full contained backup because you haven't called with the |-X 
/|method none|/| , and it doesn't get into standby because you haven't 
called with |--write-recovery-conf !|


|I believe by default it will do what you meant that you want.
|

Re: PostgreSQL vs MariaDB

[Achilleas Mantzios - cloud]

On 3/28/23 06:44, Thomas Guyot wrote:


On 2023-03-24 07:07, Inzamam Shafiq wrote:

Hi Team,

Hope you are doing well.

Can someone please list pros and cons of MariaDB vs PostgreSQL that 
actually needs serious consideration while choosing the right 
database for large OLTP DBs (Terabytes)?






Someone with a strong sysadmin background, will likely be more 
comfortable setting up and maintaining MariaDB, and some of its 
plugable engines may also be worth considering, but that really depend 
on the type of load and hardware you will be using.


I believe this as well. Also PostgreSQL is more loved by developers due 
to its academic and scientific origins.


Regarding the rest of commercial DB systems, using my PgSQL experience I 
had no problems living with MS SQL Server, and I kinda felt at home when 
it came to monitor MS SQL Server as well. Of course our main DB is 
PostgreSQL.




Thomas