Re: FATAL: could not load library "/usr/pgsql-13/lib/libpqwalreceiver.so" during replication

[Devrim Gündüz]

Hi,

On Sun, 2020-12-20 at 06:23 +0530, Rambabu V wrote:
> [root@node2 ~]# cat /etc/redhat-release
> Red Hat Enterprise Linux Server release 7.2 (Maipo)

IIRC RHEL 7 RPMs support RHEL 7.6+. Please update.

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Changing the data directory path before the initial configuration of PostgreSQL 10.7.1

[Keith Christian]

Hi Postgres friends,

Using Postgres 10.7.1 on Fedora core 28.  Admittedly not the latest
but am helping a colleague with a production system who needs a
PostgreSQL instance on this machine.

The default /var/lib/pgsql/data directory needs to go on another
partition with more space, let's call it /xyz, so the desired data
path during configuration is /xyz/var/lib/pgsql/data.

Is it possible to edit one of the conf files and replace
/var/lib/pgsql/data with /xyz/var/lib/pgsql/data before the
/usr/bin/postgresql-setup --initdb command is run?

Or, does /usr/bin/postgresql-setup --initdb have a command line option
to configure /xyz/var/lib/pgsql/data as the desired data directory?

Thanks.

Re: Changing the data directory path before the initial configuration of PostgreSQL 10.7.1

[Devrim Gündüz]

Hi,

On Thu, 2020-12-24 at 06:35 -0700, Keith Christian wrote:

> Using Postgres 10.7.1 on Fedora core 28.  Admittedly not the latest
> but am helping a colleague with a production system who needs a
> PostgreSQL instance on this machine.
> 
> The default /var/lib/pgsql/data directory needs to go on another
> partition with more space, let's call it /xyz, so the desired data
> path during configuration is /xyz/var/lib/pgsql/data.
> 
> Is it possible to edit one of the conf files and replace
> /var/lib/pgsql/data with /xyz/var/lib/pgsql/data before the
> /usr/bin/postgresql-setup --initdb command is run?

The easiest way is moving /var/lib/pgsql/data under /xyz directory
(when the db is not running) and create a symlink in the original
location:

chown postgres: /xyz
su - postgres
mv /var/lib/pgsql/data /xyz/10-data
ln -s /xyz/10-data /var/lib/pgsql/data

Then you can use setup script as usual.

Another alternative is creating a copy of the unit file under
/etc/systemd/system, changing $PGDATA inside that file and run the
setup script. This should be documented on the top of the unit file.

-HTH

Regards,


-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: Changing the data directory path before the initial configuration of PostgreSQL 10.7.1

[Keith Christian]

> The easiest way is moving /var/lib/pgsql/data under /xyz directory
> (when the db is not running) and create a symlink in the original
> location:
>
> chown postgres: /xyz
> su - postgres
> mv /var/lib/pgsql/data /xyz/10-data
> ln -s /xyz/10-data /var/lib/pgsql/data
>
> Then you can use setup script as usual.
>
> Another alternative is creating a copy of the unit file under
> /etc/systemd/system, changing $PGDATA inside that file and run the
> setup script. This should be documented on the top of the unit file.
>

Devrim,

Appreciate your quick reply and complete insructions, I'm certain that will
work!

Thank you.

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Adrian Klaver]

On 12/23/20 1:40 AM, Lars Vonk wrote:

The full setup is:

**Before:
11 primary -> 11 hotstandby binary

**During migration
11 primary -> 11 hotstandby binary
   | -> 12 new instance via logical
   |-> 12 new replica via binary

**After migration
12 primary
|-> 12 replica via binary




There are several moving parts here. I have to believe the problem is 
related. Just not sure how to figure it out after the fact. The best I 
can come up with is retry the process and monitor closely in real or 
near real time to see if you can catch the issue. Another option is to 
reduce the parts count by not running the binary 12 --> 12 replication 
at the same time you are doing the 11 --> 12 logical replication.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Problem with ssl and psql in Postgresql 13

[Tom Lane]

I wrote:
> Kyotaro Horiguchi  writes:
>> The attached the first patch does that.

> +1, it seems like a clear oversight that the GSSENC patches didn't adjust
> these messages.  The reason SSL state is mentioned is that it's relevant
> to which pg_hba entry gets chosen; and once we invented "hostgssenc"
> entries, GSSENC state is also relevant.

Thinking a little more about that: there are not four distinct states,
because GSS and SSL can't both be enabled (ProcessStartupPacket enforces
that).  So I propose that instead of adding a new field, we make the
existing field say one of three things: "GSS encryption", "SSL
encryption", or "no encryption".  As per attached.  In the back branches,
it might be best to spell these as "GSS encryption", "SSL on", and "SSL
off", just to minimize the cosmetic change.

regards, tom lane

diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 3d80930968..45572161cb 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -412,44 +412,37 @@ ClientAuthentication(Port *port)
 			 */
 			{
 char		hostinfo[NI_MAXHOST];
+const char *encryption_state;
 
 pg_getnameinfo_all(&port->raddr.addr, port->raddr.salen,
    hostinfo, sizeof(hostinfo),
    NULL, 0,
    NI_NUMERICHOST);
 
-if (am_walsender)
-{
+encryption_state =
+#ifdef ENABLE_GSS
+	port->gss->enc ? _("GSS encryption") :
+#endif
 #ifdef USE_SSL
+	port->ssl_in_use ? _("SSL encryption") :
+#endif
+	_("no encryption");
+
+if (am_walsender)
 	ereport(FATAL,
 			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+	/* translator: last %s describes encryption state */
 			 errmsg("pg_hba.conf rejects replication connection for host \"%s\", user \"%s\", %s",
 	hostinfo, port->user_name,
-	port->ssl_in_use ? _("SSL on") : _("SSL off";
-#else
-	ereport(FATAL,
-			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
-			 errmsg("pg_hba.conf rejects replication connection for host \"%s\", user \"%s\"",
-	hostinfo, port->user_name)));
-#endif
-}
+	encryption_state)));
 else
-{
-#ifdef USE_SSL
 	ereport(FATAL,
 			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+	/* translator: last %s describes encryption state */
 			 errmsg("pg_hba.conf rejects connection for host \"%s\", user \"%s\", database \"%s\", %s",
 	hostinfo, port->user_name,
 	port->database_name,
-	port->ssl_in_use ? _("SSL on") : _("SSL off";
-#else
-	ereport(FATAL,
-			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
-			 errmsg("pg_hba.conf rejects connection for host \"%s\", user \"%s\", database \"%s\"",
-	hostinfo, port->user_name,
-	port->database_name)));
-#endif
-}
+	encryption_state)));
 break;
 			}
 
@@ -465,12 +458,22 @@ ClientAuthentication(Port *port)
 			 */
 			{
 char		hostinfo[NI_MAXHOST];
+const char *encryption_state;
 
 pg_getnameinfo_all(&port->raddr.addr, port->raddr.salen,
    hostinfo, sizeof(hostinfo),
    NULL, 0,
    NI_NUMERICHOST);
 
+encryption_state =
+#ifdef ENABLE_GSS
+	port->gss->enc ? _("GSS encryption") :
+#endif
+#ifdef USE_SSL
+	port->ssl_in_use ? _("SSL encryption") :
+#endif
+	_("no encryption");
+
 #define HOSTNAME_LOOKUP_DETAIL(port) \
 (port->remote_hostname ? \
  (port->remote_hostname_resolv == +1 ? \
@@ -493,41 +496,22 @@ ClientAuthentication(Port *port)
 	0))
 
 if (am_walsender)
-{
-#ifdef USE_SSL
 	ereport(FATAL,
 			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+	/* translator: last %s describes encryption state */
 			 errmsg("no pg_hba.conf entry for replication connection from host \"%s\", user \"%s\", %s",
 	hostinfo, port->user_name,
-	port->ssl_in_use ? _("SSL on") : _("SSL off")),
+	encryption_state),
 			 HOSTNAME_LOOKUP_DETAIL(port)));
-#else
-	ereport(FATAL,
-			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
-			 errmsg("no pg_hba.conf entry for replication connection from host \"%s\", user \"%s\"",
-	hostinfo, port->user_name),
-			 HOSTNAME_LOOKUP_DETAIL(port)));
-#endif
-}
 else
-{
-#ifdef USE_SSL
 	ereport(FATAL,
 			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+	/* translator: last %s describes encryption state */
 			 errmsg("no pg_hba.conf entry for host \"%s\", user \"%s\", database \"%s\", %s",
 	hostinfo, port->user_name,
 	port->database_name,
-	port->ssl_in_use ? _("SSL on") : _("SSL off")),
-			 HOSTNAME_LOOKUP_DETAIL(port)));
-#else
-	ereport(FATAL,
-			(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
-			 errmsg("no pg_hba.conf entry for host \"%s\", user \"%s\", database \"%s\"",
-	hostinfo, port->user_name,
-	port->database_name),
+	encrypti

created type not found in create table

[Open _]

I'm using I have psql (PostgreSQL) 13.1 on centOS 7.1I do everything through 
layers of bash scripts and bash script functions.
So posting all the code would be huge.
The scripts run as postgres

Each script step tests if that object already exists before creating
Each statement is a separate call to psqlSo each statement is a separate session

The steps are:  create the database users
  created a database "StaffDB"  (Yes with capitial letters because I want it 
that way)  created a schema "staffadmin"  (ok I gave in and used lowercase for 
all except DB names)  set the search_path  create 3 types  create a table using 
those 3 types     and it says type not found.
Here is the output:
2020-12-24 16:18:54:  - bootstrap_StaffDB is running as postgres
2020-12-24 16:18:54: Step 0 - Create database users
2020-12-24 16:18:54: Creating StaffDB users
2020-12-24 16:18:55:  OptimusPrime already exists
2020-12-24 16:18:55:  Minerva already exists
2020-12-24 16:18:55:  BKuserUP already exists
2020-12-24 16:18:55:  StaffDB users complete
2020-12-24 16:18:55: StaffDB Step 1 Create StaffDB database
2020-12-24 16:18:55:   Start of create_database function for database: StaffDB
2020-12-24 16:18:55:    Creating the StaffDB Database

   \set ON_ERROR_STOP on
   CREATE DATABASE "StaffDB"
 with owner "XX" ;
   CREATE DATABASE

2020-12-24 16:18:55:    StaffDB database created
2020-12-24 16:18:55: StaffDB Step 2 - grant database level perms

   \set ON_ERROR_STOP on
   grant all privileges on database "StaffDB" to "XX";
   GRANT
   grant connect, temp  on database "StaffDB" to "YY";
   GRANT
   grant connect, temp  on database "StaffDB" to "ZZ";
   GRANT

2020-12-24 16:18:55: StaffDB Step 3 - create StaffAdmin schema and objects
2020-12-24 16:18:55:  Running 
/home/solid/DB/schema/postgres/StaffDB/staffadmin/boot_schema_StaffAdmin.inc
2020-12-24 16:18:55:  Starting to boot the StaffDB.staffadmin schema
2020-12-24 16:18:55: StaffDB:staffadmin - Step 1 - create schema
2020-12-24 16:18:55:   Start of create_schema function for database: StaffDB
2020-12-24 16:18:55:    Creating the StaffDB.staffadmin schema

   \set ON_ERROR_STOP on
   CREATE SCHEMA if not exists staffadmin
  authorization "XX";
   CREATE SCHEMA

2020-12-24 16:18:55:    StaffDB.staffadmin schema created

   \set ON_ERROR_STOP on
 show search_path ;
  search_path
   -
    "$user", public
   (1 row)


   \set ON_ERROR_STOP on
 alter role postgres in database "StaffDB" set search_path = 'staffadmin';
   ALTER ROLE


   \set ON_ERROR_STOP on
 show search_path ;
    search_path
   -
    staffadmin
   (1 row)

 2020-12-24 16:18:55: StaffDB:staffadmin - Step 2 - grant schema privileges
2020-12-24 16:18:55: StaffDB:staffadmin - Step 3 - Create StaffAdmin Types
2020-12-24 16:18:55:   Start of create_type function for StaffDB nully
2020-12-24 16:18:55:    Creating the StaffDB nully type

   \set ON_ERROR_STOP on
   CREATE TYPE staffadmin.nully
   as ENUM ('','Y') ;
   CREATE TYPE

2020-12-24 16:18:55:    StaffDB nully type created
2020-12-24 16:18:55:   Start of create_type function for StaffDB staff_roll
2020-12-24 16:18:55:    Creating the StaffDB staff_roll type

   \set ON_ERROR_STOP on
   CREATE TYPE staffadmin.staff_roll
   as ENUM ('Not yet defined', 'Everything', 'Sys Admin', 'Developer', 
'DBA', 'Security', 'Art & Apperance', 'Support', 'Manager') ;
   CREATE TYPE

2020-12-24 16:18:55:    StaffDB staff_roll type created
2020-12-24 16:18:55:   Start of create_type function for StaffDB staff_status
2020-12-24 16:18:55:    Creating the StaffDB staff_status type

   \set ON_ERROR_STOP on
   CREATE TYPE staffadmin.staff_status
   as ENUM ('New since','Active since', 'Off-line until', 'Exited on' ) ;
   CREATE TYPE

2020-12-24 16:18:55:    StaffDB staff_status type created
2020-12-24 16:18:55: StaffDB:staffadmin - Step 4 - Create Staff Table
2020-12-24 16:18:55:  Starting create_table_staff.inc
2020-12-24 16:18:55:   Creating the staffdb.staffadmin.staff table

   \set ON_ERROR_STOP on
   CREATE  TABLE IF NOT EXISTS staffadmin.staff (
 staff_id   serial          NOT NULL ,
 shortname varCHAR(12)  NOT null ,
 fullname  varCHAR(48)  NOT null ,
 created timeSTAMP  NOT null ,
 role   staffadmin.staff_role   NOT null ,
 status staffadmin.staff_status NOT null ,
 status_date  DATE  NOT null ,
 email varCHAR(60)  NOT null ,
 email_verified staffadmin.nully    null ,
 login_cnt integer  NOT null DEFAULT '0',
 last_login  timeSTAMP  DEFAULT null );
   psql:/tmp/psql_tmp.5133.sql:16: ERROR:  type "staffadmin.staff_role" does 
not exist
   LINE 9:   role   staffadmin.staff_role   NOT null ,
    ^

2020-12-24 16:18:56: __ ERROR __ Statement Failure While creating 
St

Re: created type not found in create table

[Tom Lane]

Open _  writes:
>    CREATE TYPE staffadmin.staff_roll
>    as ENUM ('Not yet defined', 'Everything', 'Sys Admin', 'Developer', 
> 'DBA', 'Security', 'Art & Apperance', 'Support', 'Manager') ;
>    CREATE TYPE

> ...

>    psql:/tmp/psql_tmp.5133.sql:16: ERROR:  type "staffadmin.staff_role" does 
> not exist
>    LINE 9:   role   staffadmin.staff_role   NOT null ,
>     ^


Uh, you didn't spell it that way before.

regards, tom lane

Re: created type not found in create table

[Open _]

 oh, duh..  works now...  Thanks for the second pair of eyes.
I thought I had ruled that out by doing a search on the type name and making 
sure it was the same all places... (which is a long ingrained habit). Must have 
searched on one of the other type names.
This is my first use of such a type in a postgres table,  thought it was 
something I didn't understand yet.



On Thursday, December 24, 2020, 12:25:29 PM EST, Tom Lane 
 wrote:  
 
 Open _  writes:
>    CREATE TYPE staffadmin.staff_roll
>    as ENUM ('Not yet defined', 'Everything', 'Sys Admin', 'Developer', 
> 'DBA', 'Security', 'Art & Apperance', 'Support', 'Manager') ;
>    CREATE TYPE

> ...

>    psql:/tmp/psql_tmp.5133.sql:16: ERROR:  type "staffadmin.staff_role" does 
> not exist
>    LINE 9:   role   staffadmin.staff_role   NOT null ,
>     ^


Uh, you didn't spell it that way before.

            regards, tom lane  

Re: pg_upgrade from 12 to 13 failes with plpython2

[Devrim Gündüz]

Hi,

On Tue, 2020-11-17 at 14:30 -0800, Adrian Klaver wrote:
> As a packager you are in charge of how the packaging is done. Still 
> announcing a change that effectively nullifies the documentation
> would to me be something that should be announced somewhere else 
> than a list  that I'm guessing 99% of the users don't read.

Published a blog post today:

https://people.planetpostgresql.org/devrim/index.php?/archives/106-What-is-new-in-PostgreSQL-13-RPMs.html

Same text will go to yum.postgresql.org tomorrow.

(Sorry for the delay)

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Lars Vonk]

Well thanks for taking the time anyway. Indeed next time reduce the parts
is a good idea.

I would still expect though that if a logical replica misses a WAL it would
stop replicating (and / or report an inconsistent state). I know this is
the case with binary replication (it stops replication).
As a last question, do you know if this is also the case with logical
replication as well, or is what happened here an "expected outcome" when a
logical replica misses a WAL?

Lars

On Thu, Dec 24, 2020 at 5:52 PM Adrian Klaver 
wrote:

> On 12/23/20 1:40 AM, Lars Vonk wrote:
> > The full setup is:
> >
> > **Before:
> > 11 primary -> 11 hotstandby binary
> >
> > **During migration
> > 11 primary -> 11 hotstandby binary
> >| -> 12 new instance via logical
> >|-> 12 new replica via binary
> >
> > **After migration
> > 12 primary
> > |-> 12 replica via binary
> >
> >
>
> There are several moving parts here. I have to believe the problem is
> related. Just not sure how to figure it out after the fact. The best I
> can come up with is retry the process and monitor closely in real or
> near real time to see if you can catch the issue. Another option is to
> reduce the parts count by not running the binary 12 --> 12 replication
> at the same time you are doing the 11 --> 12 logical replication.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Adrian Klaver]

On 12/24/20 12:24 PM, Lars Vonk wrote:
Well thanks for taking the time anyway. Indeed next time reduce the 
parts is a good idea.


I would still expect though that if a logical replica misses a WAL it 
would stop replicating (and / or report an inconsistent state). I know 
this is the case with binary replication (it stops replication).
As a last question, do you know if this is also the case with logical 
replication as well, or is what happened here an "expected outcome" when 
a logical replica misses a WAL?


It is still not clear to me what of the process was complaining about 
the WAL. Without knowing that any answer as to what effect it had would 
just be pulled out of thin air.


As to logical replication and WAL read this thread(I thought I 
remembered a previous discussion on this, took me a bit to pull it up):


https://www.postgresql.org/message-id/CAGvVEFvq_VM9LhYPeu%2BUw__gEVvrBffGL%3DFO-88cZEp-35%2BarA%40mail.gmail.com



Lars

On Thu, Dec 24, 2020 at 5:52 PM Adrian Klaver > wrote:


On 12/23/20 1:40 AM, Lars Vonk wrote:
 > The full setup is:
 >
 > **Before:
 > 11 primary -> 11 hotstandby binary
 >
 > **During migration
 > 11 primary -> 11 hotstandby binary
 >    | -> 12 new instance via logical
 >    |-> 12 new replica via binary
 >
 > **After migration
 > 12 primary
 > |-> 12 replica via binary
 >
 >

There are several moving parts here. I have to believe the problem is
related. Just not sure how to figure it out after the fact. The best I
can come up with is retry the process and monitor closely in real or
near real time to see if you can catch the issue. Another option is to
reduce the parts count by not running the binary 12 --> 12 replication
at the same time you are doing the 11 --> 12 logical replication.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Problem with ssl and psql in Postgresql 13

[Kyotaro Horiguchi]

At Thu, 24 Dec 2020 11:54:32 -0500, Tom Lane  wrote in 
> I wrote:
> > Kyotaro Horiguchi  writes:
> >> The attached the first patch does that.
> 
> > +1, it seems like a clear oversight that the GSSENC patches didn't adjust
> > these messages.  The reason SSL state is mentioned is that it's relevant
> > to which pg_hba entry gets chosen; and once we invented "hostgssenc"
> > entries, GSSENC state is also relevant.
> 
> Thinking a little more about that: there are not four distinct states,
> because GSS and SSL can't both be enabled (ProcessStartupPacket enforces
> that).  So I propose that instead of adding a new field, we make the
> existing field say one of three things: "GSS encryption", "SSL
> encryption", or "no encryption".  As per attached.  In the back branches,
> it might be best to spell these as "GSS encryption", "SSL on", and "SSL
> off", just to minimize the cosmetic change.

Looks good to me.

I tried the same case where

- did kinit
- pg_hba has hostssl line only

I saw the following lines in server log, which seems good.

FATAL:  no pg_hba.conf entry for host "192.168.56.101", user 
"horig...@mydomain.com", database "postgres", GSS encryption
FATAL:  no pg_hba.conf entry for host "192.168.56.101", user 
"horig...@mydomain.com", database "postgres", no encryption

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Stats for indexes on expressions

[Shantanu Shekhar]

Hello,
I am trying to understand how the cost for a query involving indexes on 
expressions is calculated. How is the statistics on the expression maintained? 
For example Postgres documentation on 'Indexes on Expressions' mentions the 
following example:
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));SELECT * 
FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
The index is created on an expression involving first name and last name. My 
confusion is basically around:
(1) When using this index how will the query planner get the stats on this 
expression? (2) Whenever an index on an expression is defined like this, should 
we rebuild the stats? 
Thanks,
Shantanu

Re: Stats for indexes on expressions

[Tom Lane]

Shantanu Shekhar  writes:
> I am trying to understand how the cost for a query involving indexes on 
> expressions is calculated. How is the statistics on the expression 
> maintained? For example Postgres documentation on 'Indexes on Expressions' 
> mentions the following example:
> CREATE INDEX people_names ON people ((first_name || ' ' || last_name));SELECT 
> * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
> The index is created on an expression involving first name and last name. My 
> confusion is basically around:
> (1) When using this index how will the query planner get the stats on this 
> expression?

ANALYZE on a table will (re)build statistics for any expression indexes on
that table, in addition to the stats for the table's own columns.

(2) Whenever an index on an expression is defined like this, should we rebuild 
the stats?

If you don't want to wait around for auto-analyze to do it, yes.

regards, tom lane