Re: PCI:SSF - Safe SQL Query & operators filter

[Jan Bilek]

On 11/8/22 17:03, Laurenz Albe wrote:
> On Tue, 2022-11-08 at 04:14 +, Jan Bilek wrote:
>
>> I know it is not exactly what you suggested (and agreeing a lot with our
>> app user shouldn't be running as superuser), but as all other inputs
>> from our application come sanitized through bind and this is the only
>> way where user can send an explicit command in there - I think it should do!
>>
>> Please let me know if you approve.
> I strongly disapprove, and any security audit you pass with such a setup
> is worthless.  I repeat: the application does not need to connect with
> a superuser.
>
> I don't understand what you want to demonstrate with the code samples, or
> what you mean when you say that "the user can send an explicit command".
>
> Yours,
> Laurenz Albe

Interesting.

I agree that our app shouldn't need superuser, but that would mean that 
some ... you made me give it some serious though here.

Installation itself is happening under elevated (root) rights. We are 
using the postgres account for moving in all what's needed (e.g. that 
plpython3u extension). Walking though our code for most of the day, I 
can't see why that superuser would be really needed. Those plpython3u 
functions are wrapped up under the hood already. I'm sending that in to 
check if our QA will find anything.

Thanks for being stubborn about this!

Cheers,
Jan

-- 
Jan Bilek - CTO at EFTlab Pty Ltd.

Re: Segmentation Fault PG 14

[Thomas Munro]

On Tue, Nov 8, 2022 at 11:45 AM Willian Colognesi
 wrote:
> root@ip-10-x-x-x:/home/ubuntu# pg_config --configure
> ... --with-extra-version= (Ubuntu 14.5-2.pgdg20.04+2)' ...
> ... '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-10' ...

> There is no llvm installed on ubuntu server, postgresql was installed via apt 
> package `apt install postgresql-14`

We can see from the pg_config output that it's built with LLVM 10.
Also that looks like it's the usual pgdg packages which are certainly
built against LLVM and will install it automatically.

Re: PCI:SSF - Safe SQL Query & operators filter

[Laurenz Albe]

On Tue, 2022-11-08 at 04:14 +, Jan Bilek wrote:

> I know it is not exactly what you suggested (and agreeing a lot with our 
> app user shouldn't be running as superuser), but as all other inputs 
> from our application come sanitized through bind and this is the only 
> way where user can send an explicit command in there - I think it should do!
> 
> Please let me know if you approve.

I strongly disapprove, and any security audit you pass with such a setup
is worthless.  I repeat: the application does not need to connect with
a superuser.

I don't understand what you want to demonstrate with the code samples, or
what you mean when you say that "the user can send an explicit command".

Yours,
Laurenz Albe

Re: My account was locked in pgadmin4

[Ashesh Vashi]

Please send your message to pgadmin-supp...@postgresql.org for getting
suggestions on pgAdmin 4.


--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company



*http://www.linkedin.com/in/asheshvashi*



On Mon, Nov 7, 2022 at 11:45 PM William Torrez Corea 
wrote:

> I reset the password but i don't receive any message
>
> --
>
> With kindest regards, William.
>
> ⢀⣴⠾⠻⢶⣦⠀
> ⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system
> ⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org
> ⠈⠳⣄
>
>
>

Re: PCI:SSF - Safe SQL Query & operators filter

[Jan Bilek]

On 11/8/22 11:50, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:43, Jan Bilek  wrote:
>>
>> Well, superuser (our App) is already logged in and as it is designed
>> very much as an "appliance" it simply does that job - manages its
>> database.
> Well... don't do that. :)  The problem is analogous to having root log into a 
> Linux box and run application commands.  It works, but it opens a security 
> hole, as you've discovered.
>
>> Yes, agreed. Any ideas?
> In this particular case (creating an untrusted PL and functions therein), 
> you'll need to use a PostgreSQL superuser.  This is a separate operation from 
> routine application use, though.  (I'll note that having functions in an 
> untrusted PL in a PCI-sensitive system is not a great idea, as you'll need to 
> audit them very closely to make sure that they can't do anything untoward 
> outside the role system.)

Thank you David, Laurentz & Christophe,

All excellent inputs.

I've realized that our reporting feature is wrapped-bound in a 
Transaction & Rollback. This actually came with an idea to Alter that 
role as a part of transaction.

It works in an excellent way!

BEGIN TRANSACTION;
alter role CURRENT_USER with NOSUPERUSER;
select * from pg_read_file('/etc/passwd' , 0 , 100);
ROLLBACK TRANSACTION;
BEGIN
ALTER ROLE
ERROR:  permission denied for function pg_read_file
ROLLBACK
bp-node=#

Even trying to break it seems to be difficult.

BEGIN TRANSACTION;
alter role CURRENT_USER with NOSUPERUSER;
alter role CURRENT_USER with SUPERUSER;
ROLLBACK TRANSACTION;
BEGIN
ALTER ROLE
ERROR:  must be superuser to alter superuser roles or change superuser 
attribute
ROLLBACK

I know it is not exactly what you suggested (and agreeing a lot with our 
app user shouldn't be running as superuser), but as all other inputs 
from our application come sanitized through bind and this is the only 
way where user can send an explicit command in there - I think it should do!

Please let me know if you approve.

Thanks & Cheers,
Jan

-- 
Jan Bilek - CTO at EFTlab Pty Ltd.

Re: postgres replication without pg_basebackup? postgres 13.3

[Laurenz Albe]

On Mon, 2022-11-07 at 23:11 +0100, Pilar de Teodoro wrote:
> Thank you very much for the idea. [of running pg_rewind]
> We ran pg_rewind correctly:
> [postgres@gacsdb05 data-13.3]$ pg_rewind -c -R 
> --target-pgdata=/PostgresDB/sas_hdd/data-13.3/  --source-server="host= 
> port= user=postgres password=XXX" 
> pg_rewind: source and target cluster are on the same timeline
> pg_rewind: no rewind required

I cannot verify that you ran it correctly.

"Target" should be the old server with the extra transactions.
"Source" should be the promoted standby server.  Promotion switches to a new 
timeline,
so it looks like you did something wrong.

> but then we got the following error:
> 
> [2022-11-07 22:57:55 CET-]LOG:  starting PostgreSQL 13.3 on 
> x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 
> 64-bit
> [2022-11-07 22:57:55 CET-]LOG:  listening on IPv4 address "0.0.0.0", port 
> [2022-11-07 22:57:55 CET-]LOG:  could not create IPv6 socket for address 
> "::": Address family not supported by protocol
> [2022-11-07 22:57:55 CET-]LOG:  listening on Unix socket "/tmp/.s.PGSQL."
> [2022-11-07 22:57:55 CET-]LOG:  database system was shut down in recovery at 
> 2022-11-07 22:57:47 CET
> [2022-11-07 22:57:55 CET-]LOG:  restored log file "0003.history" from 
> archive
> cp: cannot stat '/PostgresWalLogArchive/new/0004.history': No such file 
> or directory
> [2022-11-07 22:57:55 CET-]LOG:  entering standby mode
> [2022-11-07 22:57:55 CET-]LOG:  restored log file "0003.history" from 
> archive
> [2022-11-07 22:57:55 CET-]LOG:  invalid primary checkpoint record
> [2022-11-07 22:57:55 CET-]PANIC:  could not locate a valid checkpoint record
> [2022-11-07 22:57:55 CET-]LOG:  startup process (PID 3011860) was terminated 
> by signal 6: Aborted
> [2022-11-07 22:57:55 CET-]LOG:  aborting startup due to startup process 
> failure
> [2022-11-07 22:57:55 CET-]LOG:  database system is shut down
> 
> We have read we can run pg_resetwal but the Wal folder is the folder where 
> the Wals are archived in the primary. Would that be correct to reset them?

Don't run "pg_resetwal".  At the very least, it will break your standby.

At this point, your standby seems to be broken.  I don't know what exactly you
did, but it leeks like you should run a "pg_basebackup" after all.

Yours,
Laurenz Albe

Re: PCI:SSF - Safe SQL Query & operators filter

[Laurenz Albe]

On Tue, 2022-11-08 at 01:24 +, Jan Bilek wrote:
> I am leading PCI:SSF audit on our payment switch platform product and having 
> a issue where our QSA
> just came with multiple ways how to escalate privileges and get a shell to 
> the remote host through
> our built-in Reporting system which runs on PostgreSQL (12-14).

> So these - pg_read_server_files, pg_write_server_files and 
> pg_execute_server_program roles are
> inherited from the Superuser and are also super sensitive for us, but our 
> application user apparently
> still needs to be superuser for multiple reasons. Would there be any way to 
> go around this?

Your application user doesn't need to be a superuser.

> 1/ What we need is to create a postgresql user who would be pg_database_owner 
> but not superuser ...
>but apparently we still need our user to be superuser to be able to carry 
> on with some operations
>   (picking one of more then few) 
> CREATE OR REPLACE LANGUAGE plpython3u; 
> HINT:  Must be superuser to create this extension.

Your application user doesn't need to create PL/Python functions.  It only has 
to use them.

If your application has to access the operating system, it has to do that in a 
controlled
fashion:

1. through well-written SECURITY DEFINER functions that belong to a highly 
privileged user

2. through PL/Python functions or other untrusted language functions that are 
created
   by a superuser

> 2/ Second option is to introduce some sort of limitation of allowed SQL 
> queries and operators
>for our Reporting engine, but all that white-listing / blacklisting comes 
> with its own
>problems where we are DB agnostic...

That shouldn't be necessary.  If your application user has restricted 
privileges as it should,
it is automatically limited in the damage it can do.

Yours,
Laurenz Albe

Re: PCI:SSF - Safe SQL Query & operators filter

[Jeffrey Walton]

On Mon, Nov 7, 2022 at 8:25 PM Jan Bilek  wrote:
> ...
> select * from pg_read_file('/etc/passwd' , 0 , 100); -> it's possible to 
> display content of '/etc/passwd/' file
> select version(); -> Result of DBMS version request.

Input filtering may help in the interim, until you get the roles and
privileges sorted out.

> COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; -> it's 
> possible to create files on the filesystem on behalf of 'postgres' user.

Prepared Statement or Parameterized Query here. In this case, don't
execute data as code.

Jeff

Re: PCI:SSF - Safe SQL Query & operators filter

[David G. Johnston]

On Mon, Nov 7, 2022 at 6:25 PM Jan Bilek  wrote:

> The main problem comes from obvious - our application's PostgreSQL user
> needs to have an Superuser role as it manages most of its (dedicated)
> database (creates tables, drops those, manages views, triggers ... ).
>
No, the things you want to manage within the database do not require
Superuser.  You only need superuser once to configure the system in such a
way, through role and grants and possibly default permissions, that from
then on most everything an application user would want to do can be done by
the role(s) you have created.

> So these - pg_read_server_files, pg_write_server_files and
> pg_execute_server_program roles are inherited from the Superuser
>
Those roles are entirely independent of Superuser; and in fact exists for
that very purpose, since Superuser can already do those things.

and are also super sensitive for us, but our application user apparently
> still needs to be superuser for multiple reasons.
>
You will need to be specific as to exactly what capability you need that
you cannot get a non-superuser role to accomplish.

1/ What we need is to create a postgresql user who would be
> pg_database_owner but not superuser
>
OWNER is an attribute of specific objects, including the database object.
You can already do this.




> ... but apparently we still need our user to be superuser to be able to
> carry on with some operations (picking one of more then few) 
>
> CREATE OR REPLACE LANGUAGE plpython3u;
> HINT:  Must be superuser to create this extension.
>

Yeah, this will be a bit tougher since you are in conflict regarding
whether you need O/S resources or not.  It is hard to solve this problem
without the bigger picture.


> 2/ Second option is to introduce some sort of limitation of allowed SQL
> queries
>
A useful tool is "SECURITY DEFINER" tagged routines.

The lack of a separate database migration process seems to be your main
issue - something that can run audited code in an elevated context to put
the system into a state where non-elevated users can then do the things
they need to do.  Even if you just have modes in your application that are
"admin mode" and "user mode" so admin mode can do the, hopefully limited,
subset of actions that need superuser while user mode operates under the
only semi-powerful database owner role you can probably bridge the gap (if
you indeed must have the database do things directly on the underlying
operating system).

David J.

Re: PCI:SSF - Safe SQL Query & operators filter

[Christophe Pettus]

> On Nov 7, 2022, at 17:43, Jan Bilek  wrote:
> 
> Well, superuser (our App) is already logged in and as it is designed 
> very much as an "appliance" it simply does that job - manages its 
> database.

Well... don't do that. :)  The problem is analogous to having root log into a 
Linux box and run application commands.  It works, but it opens a security 
hole, as you've discovered.

> Yes, agreed. Any ideas?

In this particular case (creating an untrusted PL and functions therein), 
you'll need to use a PostgreSQL superuser.  This is a separate operation from 
routine application use, though.  (I'll note that having functions in an 
untrusted PL in a PCI-sensitive system is not a great idea, as you'll need to 
audit them very closely to make sure that they can't do anything untoward 
outside the role system.)

Re: PCI:SSF - Safe SQL Query & operators filter

[Jan Bilek]

On 11/8/22 11:29, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:24, Jan Bilek  wrote:
>> Would there be any way to go around this?
> The typical configuration is to not permit the PostgreSQL superuser to log in 
> remotely.  The database can be managed by a different, non-superuser role, 
> including schema migrations.
Well, superuser (our App) is already logged in and as it is designed 
very much as an "appliance" it simply does that job - manages its 
database. There might be a way to explore whether we can use internally 
another (limited) user just for reporting queries on a different 
connection session. But I am getting (security related) headaches just 
thinking about it.
>
>> CREATE OR REPLACE LANGUAGE plpython3u;
>> HINT:  Must be superuser to create this extension.
> The reason only a superuser can create this extension is the "u" at the end 
> of the name: It is an untrusted PL that can bypass PostgreSQL's role system.  
> If anyone could create functions in it, anyone could bypass roles.

Yes, agreed. Any ideas?

-- 
Jan Bilek - CTO at EFTlab Pty Ltd.

Re: PCI:SSF - Safe SQL Query & operators filter

[Christophe Pettus]

> On Nov 7, 2022, at 17:24, Jan Bilek  wrote:
> Would there be any way to go around this?

The typical configuration is to not permit the PostgreSQL superuser to log in 
remotely.  The database can be managed by a different, non-superuser role, 
including schema migrations.

> CREATE OR REPLACE LANGUAGE plpython3u; 
> HINT:  Must be superuser to create this extension.

The reason only a superuser can create this extension is the "u" at the end of 
the name: It is an untrusted PL that can bypass PostgreSQL's role system.  If 
anyone could create functions in it, anyone could bypass roles.

PCI:SSF - Safe SQL Query & operators filter

[Jan Bilek]

Hi team,

I am leading PCI:SSF audit on our payment switch platform product and having a 
issue where our QSA just came with multiple ways how to escalate privileges and 
get a shell to the remote host through our built-in Reporting system which runs 
on PostgreSQL (12-14).

  1.  select * from pg_read_file('/etc/passwd' , 0 , 100); -> it's possible 
to display content of '/etc/passwd/' file
  2.  select version(); -> Result of DBMS version request.
  3.  COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; -> it's 
possible to create files on the filesystem on behalf of 'postgres' user.

The main problem comes from obvious - our application's PostgreSQL user needs 
to have an Superuser role as it manages most of its (dedicated) database 
(creates tables, drops those, manages views, triggers ... ).

This all matches description from documentation as follows:



The pg_read_server_files, pg_write_server_files and pg_execute_server_program 
roles are intended to allow administrators to have trusted, but non-superuser, 
roles which are able to access files and run programs on the database server as 
the user the database runs as. As these roles are able to access any file on 
the server file system, they bypass all database-level permission checks when 
accessing files directly and they could be used to gain superuser-level access, 
therefore great care should be taken when granting these roles to users.



So these - pg_read_server_files, pg_write_server_files and 
pg_execute_server_program roles are inherited from the Superuser and are also 
super sensitive for us, but our application user apparently still needs to be 
superuser for multiple reasons. Would there be any way to go around this?

1/ What we need is to create a postgresql user who would be pg_database_owner 
but not superuser ... but apparently we still need our user to be superuser to 
be able to carry on with some operations (picking one of more then few) 

CREATE OR REPLACE LANGUAGE plpython3u;
HINT:  Must be superuser to create this extension.

2/ Second option is to introduce some sort of limitation of allowed SQL queries 
and operators for our Reporting engine, but all that white-listing / 
blacklisting comes with its own problems where we are DB agnostic...

I am sorry for a long email, but any ideas/pointers will be greatly appreciated.

Thank you & Kind Regards,
Jan

--
Jan Bilek - CTO at EFTlab Pty Ltd.

Re: Segmentation Fault PG 14

[Jeffrey Walton]

On Mon, Nov 7, 2022 at 2:38 PM Tom Lane  wrote:
>
> Willian Colognesi  writes:
> > `I take it things were okay with the version you used previously?`
>
> > Yes, it was working pretty well in another instance with pg version
> > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that
> > was running in this server to another using Logical Replication.
>
> 12.4 to 14.5 is kind of a big jump :-(.
>
> The stack trace seems to indicate that ExecProcNode transferred control
> to never-never land, which says that something clobbered the function
> pointer it's trying to indirect through.  I don't recall having seen
> any similar reports though.

I'm just thinking out loud... I've seen the latest GCC do that on what
it believes to be dead code. Our problem was detailed at
https://github.com/weidai11/cryptopp/issues/1141 .

We identified the problem by building/running our self tests with
-fsanitize=unreachable .

Testing with -fsanitize=unreachable should confirm or rule out GCC and
Clang [incorrectly] removing code that is actually needed. If this is
the problem, then -fsanitize=unreachable will also provide a usable
stack trace and provide a useful debugging experience.

Jeff

Re: Segmentation Fault PG 14

[Tom Lane]

Willian Colognesi  writes:
> There is no llvm installed on ubuntu server, postgresql was installed via
> apt package `apt install postgresql-14`

If there's no LLVM around, then disabling JIT wouldn't do anything,
because it depends on LLVM to compile code.

We should perhaps wait awhile to see if that really fixed it.

regards, tom lane

Re: Segmentation Fault PG 14

[Willian Colognesi]

Do you mean how it was compiled? the output of pg_config is it:
```
root@ip-10-x-x-x:/home/ubuntu# pg_config --configure
 '--build=aarch64-linux-gnu' '--prefix=/usr'
'--includedir=${prefix}/include' '--mandir=${prefix}/share/man'
'--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var'
'--disable-silent-rules' '--libdir=${prefix}/lib/aarch64-linux-gnu'
'--runstatedir=/run' '--disable-maintainer-mode'
'--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python'
'--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt'
'--mandir=/usr/share/postgresql/14/man'
'--docdir=/usr/share/doc/postgresql-doc-14'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/14' '--bindir=/usr/lib/postgresql/14/bin'
'--libdir=/usr/lib/aarch64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu
14.5-2.pgdg20.04+2)' '--enable-nls' '--enable-thread-safety'
'--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs'
'--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432'
'--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir
-p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar'
'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fstack-protector-strong
-Wformat -Werror=format-security' 'LDFLAGS=-Wl,-Bsymbolic-functions
-Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--*with-llvm'
'LLVM_CONFIG=/usr/bin/llvm-config-10*' 'CLANG=/usr/bin/clang-10'
'--with-lz4' '--with-systemd' '--with-selinux'
'build_alias=aarch64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2'
'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security'
```

There is no llvm installed on ubuntu server, postgresql was installed via
apt package `apt install postgresql-14`

On Mon, Nov 7, 2022 at 6:09 PM Tom Lane  wrote:

> Willian Colognesi  writes:
> > No, the database is running well, no problem until now after disabled
> *jit.*
>
> Interesting.  Which version of LLVM is installed?
>
> regards, tom lane
>


-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: postgres replication without pg_basebackup? postgres 13.3

[Pilar de Teodoro]

Dear Laurenz,
Thank you very much for the idea.
We ran pg_rewind correctly:

[postgres@gacsdb05 data-13.3]$ pg_rewind -c -R
--target-pgdata=/PostgresDB/sas_hdd/data-13.3/  --source-server="host=
port= user=postgres password=XXX"

pg_rewind: source and target cluster are on the same timeline

pg_rewind: no rewind required


but then we got the following error:


[2022-11-07 22:57:55 CET-]LOG:  starting PostgreSQL 13.3 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat
8.5.0-10), 64-bit

[2022-11-07 22:57:55 CET-]LOG:  listening on IPv4 address "0.0.0.0", port


[2022-11-07 22:57:55 CET-]LOG:  could not create IPv6 socket for address
"::": Address family not supported by protocol

[2022-11-07 22:57:55 CET-]LOG:  listening on Unix socket
"/tmp/.s.PGSQL."

[2022-11-07 22:57:55 CET-]LOG:  database system was shut down in recovery
at 2022-11-07 22:57:47 CET

[2022-11-07 22:57:55 CET-]LOG:  restored log file "0003.history" from
archive

cp: cannot stat '/PostgresWalLogArchive/new/0004.history': No such file
or directory

[2022-11-07 22:57:55 CET-]LOG:  entering standby mode

[2022-11-07 22:57:55 CET-]LOG:  restored log file "0003.history" from
archive

[2022-11-07 22:57:55 CET-]LOG:  invalid primary checkpoint record

[2022-11-07 22:57:55 CET-]PANIC:  could not locate a valid checkpoint record

[2022-11-07 22:57:55 CET-]LOG:  startup process (PID 3011860) was
terminated by signal 6: Aborted

[2022-11-07 22:57:55 CET-]LOG:  aborting startup due to startup process
failure

[2022-11-07 22:57:55 CET-]LOG:  database system is shut down


We have read we can run pg_resetwal but the Wal folder is the folder where
the Wals are archived in the primary. Would that be correct to reset them?


Any suggestion?

Thank you so much,


Pilar

El lun, 7 nov 2022 a las 12:21, Laurenz Albe ()
escribió:

> On Mon, 2022-11-07 at 11:02 +0100, Pilar de Teodoro wrote:
> > We have a very large database of 37TB and we had to promote our standby
> to primary due to
> > some disk failures. Now the issues are solved, we would like to make
> standby  the old primary
> > from a copy of the new primary which is already in place.  Is it
> possible without using pg_basebackup?
>
> That's exactly what "pg_rewind" is for.  It is a fast version of
> "pg_basebackup" for exactly
> that case.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>

Re: Setting up replication on Windows, v9.4

[Rob Sargent]

On 11/7/22 13:59, Brad White wrote:



> v9.4 has been EOL for 2 years 9 months. 


As I said, the next step will be to upgrade.
It would make sense to upgrade first, since "there have been some big
advances since then which make replication much easier"
But when we upgraded, the app stopped working.
So I'll need to go through and nail down which exact version causes 
the issue and then see if we can get it resolved.

I have no way of knowing how long that will take.
So we're doing the replication first.
Care to share some of the ways the app stopped working?  You might get a 
leg up on where best to remediate.

Re: Segmentation Fault PG 14

[Tom Lane]

Willian Colognesi  writes:
> No, the database is running well, no problem until now after disabled *jit.*

Interesting.  Which version of LLVM is installed?

regards, tom lane

Re: Setting up replication on Windows, v9.4

[Brad White]

>
>
> > v9.4 has been EOL for 2 years 9 months.

As I said, the next step will be to upgrade.
It would make sense to upgrade first, since "there have been some big
advances since then which make replication much easier"
But when we upgraded, the app stopped working.
So I'll need to go through and nail down which exact version causes the
issue and then see if we can get it resolved.
I have no way of knowing how long that will take.
So we're doing the replication first.

Re: copy file from a client app to remote postgres isntance

[Ron]

On 11/7/22 10:57, Вадим Самохин wrote:

Hi all,
I have an application that must copy a local file in csv format to a 
postgres table on a remote host. The closest solution is this one 
(https://stackoverflow.com/a/9327519/618020 
). It boils down to specifying 
a \copy meta-command in a psql command:

|psql -U %s -p %s -d %s -f - <

||
|and executing it. B|ut it's quite an unnatural way to write database 
code. Has anything changed in the last ten years? Or, is there a better 
wayto copy file contents in a remote database?


I'd write a small Python script, using the csv module to read the data and 
psycopg2 to load it.


--
Angular momentum makes the world go 'round.

Re: Segmentation Fault PG 14

[Willian Colognesi]

No, the database is running well, no problem until now after disabled *jit.*

I just realized that he send an email direct to me, the message was:
```
I had similar problems with and the cure was to turn off jit in
Postgres.conf

jit = off
--
Boris
```



On Mon, Nov 7, 2022 at 5:25 PM Adrian Klaver 
wrote:

> On 11/7/22 12:15, Willian Colognesi wrote:
> > All the extensions installed in this database are these:
> > ```
> >   List of installed extensions
> >  Name| Version |   Schema   |
> >   Description
> >
> +-++---
> >   amcheck| 1.3 | public | functions for verifying
> > relation integrity
> >   btree_gist | 1.6 | public | support for indexing
> > common datatypes in GiST
> >   pg_stat_statements | 1.9 | public | track execution statistics
> > of all SQL statements executed
> >   pgcrypto   | 1.3 | public | cryptographic functions
> >   plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural
> language
> > (5 rows)
> > ```
> >
> > I tried to execute a query with parameters the query was supposed to be
> > run (because I'm not sure exactly the values in the where clause that
> > made the segmentation fault).
> >
> > here is the explain: https://explain.depesz.com/s/Tql3
> >  (Ps: I just had to suppress the
> real
> > table/index names)
> >
> > Looks like since I've disable *jit* as Boris told, until now the
> > database did not restarted again... (not sure if it's coincidence)
> >
>
> I did not see that post or suggestion.
>
> What was the suggestion?
>
> Are you saying the database does not start up now?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: Segmentation Fault PG 14

[Adrian Klaver]

On 11/7/22 12:15, Willian Colognesi wrote:

All the extensions installed in this database are these:
```
                                      List of installed extensions
         Name        | Version |   Schema   |   
  Description

+-++---
  amcheck            | 1.3     | public     | functions for verifying 
relation integrity
  btree_gist         | 1.6     | public     | support for indexing 
common datatypes in GiST
  pg_stat_statements | 1.9     | public     | track execution statistics 
of all SQL statements executed

  pgcrypto           | 1.3     | public     | cryptographic functions
  plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)
```

I tried to execute a query with parameters the query was supposed to be 
run (because I'm not sure exactly the values in the where clause that 
made the segmentation fault).


here is the explain: https://explain.depesz.com/s/Tql3 
 (Ps: I just had to suppress the real 
table/index names)


Looks like since I've disable *jit* as Boris told, until now the 
database did not restarted again... (not sure if it's coincidence)




I did not see that post or suggestion.

What was the suggestion?

Are you saying the database does not start up now?

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

Re: Segmentation Fault PG 14

[Willian Colognesi]

All the extensions installed in this database are these:
```
 List of installed extensions
Name| Version |   Schema   |
 Description
+-++---
 amcheck| 1.3 | public | functions for verifying
relation integrity
 btree_gist | 1.6 | public | support for indexing common
datatypes in GiST
 pg_stat_statements | 1.9 | public | track execution statistics of
all SQL statements executed
 pgcrypto   | 1.3 | public | cryptographic functions
 plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language
(5 rows)
```

I tried to execute a query with parameters the query was supposed to be run
(because I'm not sure exactly the values in the where clause that made the
segmentation fault).

here is the explain: https://explain.depesz.com/s/Tql3 (Ps: I just had to
suppress the real table/index names)

Looks like since I've disable *jit* as Boris told, until now the database
did not restarted again... (not sure if it's coincidence)


On Mon, Nov 7, 2022 at 4:38 PM Tom Lane  wrote:

> Willian Colognesi  writes:
> > `I take it things were okay with the version you used previously?`
>
> > Yes, it was working pretty well in another instance with pg version
> > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that
> > was running in this server to another using Logical Replication.
>
> 12.4 to 14.5 is kind of a big jump :-(.
>
> The stack trace seems to indicate that ExecProcNode transferred control
> to never-never land, which says that something clobbered the function
> pointer it's trying to indirect through.  I don't recall having seen
> any similar reports though.
>
> Are you using any extensions besides those that come with core Postgres?
> A build incompatibility with some third-party extension might explain
> this, perhaps.
>
> One thing I'm curious about is that the stack trace seems to imply that
> there was an Append plan node immediately below another Append.  That
> shouldn't happen AFAIK --- the planner tries to collapse out such
> cases.  Can you get us an EXPLAIN for the problem query?
>
> regards, tom lane
>


-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: Segmentation Fault PG 14

[Tom Lane]

Willian Colognesi  writes:
> `I take it things were okay with the version you used previously?`

> Yes, it was working pretty well in another instance with pg version
> `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that
> was running in this server to another using Logical Replication.

12.4 to 14.5 is kind of a big jump :-(.

The stack trace seems to indicate that ExecProcNode transferred control
to never-never land, which says that something clobbered the function
pointer it's trying to indirect through.  I don't recall having seen
any similar reports though.

Are you using any extensions besides those that come with core Postgres?
A build incompatibility with some third-party extension might explain
this, perhaps.

One thing I'm curious about is that the stack trace seems to imply that
there was an Append plan node immediately below another Append.  That
shouldn't happen AFAIK --- the planner tries to collapse out such
cases.  Can you get us an EXPLAIN for the problem query?

regards, tom lane

Re: Segmentation Fault PG 14

[Adrian Klaver]

On 11/7/22 11:03 AM, Willian Colognesi wrote:
No, the origin where the database was was running ubuntu 18.04.5 x86_64 
and the destination ubuntu 20.04.5 aarch64


Where I was going was this:

https://wiki.postgresql.org/wiki/Locale_data_changes

Then I realized you had not done any binary upgrades, so that is a dead end.


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

Re: Segmentation Fault PG 14

[Willian Colognesi]

No, the origin where the database was was running ubuntu 18.04.5 x86_64 and
the destination ubuntu 20.04.5 aarch64

On Mon, Nov 7, 2022 at 4:00 PM Adrian Klaver 
wrote:

> On 11/7/22 10:57 AM, Willian Colognesi wrote:
> > 1) What versions of pg_dump and pg_restore did you use?
> > A: pg_dump and pg_restore was done using pg 14 (the same as the
> > destination was running)
> >
> > 2) To be clear the subscription was started after the restore?
> > A: Yes
> >
> > 3) Where there any error messages issued at any point in below?
> > A: no errors during the dump and restore.
> >
> > 4) Are the database clusters on the same machine?
> > A: No, the origin and destination were different servers at the same VPC.
>
> Are servers using the same version of OS?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: Segmentation Fault PG 14

[Adrian Klaver]

On 11/7/22 10:57 AM, Willian Colognesi wrote:

1) What versions of pg_dump and pg_restore did you use?
A: pg_dump and pg_restore was done using pg 14 (the same as the 
destination was running)


2) To be clear the subscription was started after the restore?
A: Yes

3) Where there any error messages issued at any point in below?
A: no errors during the dump and restore.

4) Are the database clusters on the same machine?
A: No, the origin and destination were different servers at the same VPC.


Are servers using the same version of OS?


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

Re: Segmentation Fault PG 14

[Willian Colognesi]

1) What versions of pg_dump and pg_restore did you use?
A: pg_dump and pg_restore was done using pg 14 (the same as the destination
was running)

2) To be clear the subscription was started after the restore?
A: Yes

3) Where there any error messages issued at any point in below?
A: no errors during the dump and restore.

4) Are the database clusters on the same machine?
A: No, the origin and destination were different servers at the same VPC.

On Mon, Nov 7, 2022 at 3:49 PM Adrian Klaver 
wrote:

> On 11/7/22 10:36 AM, Willian Colognesi wrote:
> > Hi Tom,
> >
> > `I take it things were okay with the version you used previously?`
> > Yes, it was working pretty well in another instance with pg version
> > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database
> > that was running in this server to another using Logical Replication.
>
> Actually you used dump/restore and logical replication. '
>
> In below:
>
> 1) What versions of pg_dump and pg_restore did you use?
>
> 2) To be clear the subscription was started after the restore?
>
> 3) Where there any error messages issued at any point in below?
>
> 4) Are the database clusters on the same machine?
>
> >
> > the process was basically this:
> > |CREATE| |PUBLICATION my_database_pub ||FOR| |ALL| |TABLES;|
> > |postgres@origin:~$ psql "dbname= replication=database"
> > |
> > |
> > |my_database=# CREATE_REPLICATION_SLOT  LOGICAL pgoutput;|
> > pg_dump -j4 -h  -p 5432 --no-subscriptions --no-publications -d
> >  --snapshot= -Fd -U  -f
> > 
> > postgres@destination:/mnt/database$ pg_restore -d  -j 5
> > 
> >
> > CREATE SUBSCRIPTION 
> > CONNECTION 'host= dbname= user=replica
> > password=?? port=5432'
> > PUBLICATION 
> > WITH (slot_name=, create_slot=false, copy_data=false);
> > |
> >
> >
> > After this migration we started to have this kind of problem in both
> > replica and primary servers.
> >
> > `This looks pretty messed up.  Are you sure the debug symbols you're
> using`
> > What exactly do you mean? I'm not too familiar with this debug toolings,
> > the packages I've used were:
> >
> > postgresql-14/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
> > postgresql-14-dbgsym/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
> >
> > `Even better, can you construct a self-contained test case?`:
> > Actually I couldn't reproduce the problem because it's happening just in
> > a production database, and it doesn't look to have a pattern in the
> > cases when it happens.
> >
> > Is there anything I could provide you to help the analysis ?
> >
> >
> >
> > On Mon, Nov 7, 2022 at 3:08 PM Tom Lane  > > wrote:
> >
> > Willian Colognesi  > > writes:
> >  > I started to use version `14.5-2.pgdg20.04+2` for a dedicated
> > database and
> >  > I'm facing many segmentation faults during the day when the
> > database has
> >  > more heavy queries.
> >
> > I take it things were okay with the version you used previously?
> > What was that exactly?  Has anything else changed?
> >
> >  > I could also get a little information from gdb, I'm not sure if
> > it will
> >  > help:
> >
> > This looks pretty messed up.  Are you sure the debug symbols you're
> > using
> > match the package?
> >
> > Even better, can you construct a self-contained test case?
> >
> >  regards, tom lane
> >
> >
> >
> > --
> > 
> > *Willian Cezar de O. Colognesi
> > *
> > Systems Analysis Specialist, Trimble Transportation Brazil
> > Avenida Santos Dumont, 271 | Londrina, PR | 86039-090
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: Segmentation Fault PG 14

[Adrian Klaver]

On 11/7/22 10:36 AM, Willian Colognesi wrote:

Hi Tom,

`I take it things were okay with the version you used previously?`
Yes, it was working pretty well in another instance with pg version 
`12.4-1.pgdg18.04+1`, and we had to make a migration of one database 
that was running in this server to another using Logical Replication.


Actually you used dump/restore and logical replication. '

In below:

1) What versions of pg_dump and pg_restore did you use?

2) To be clear the subscription was started after the restore?

3) Where there any error messages issued at any point in below?

4) Are the database clusters on the same machine?



the process was basically this:
|CREATE| |PUBLICATION my_database_pub ||FOR| |ALL| |TABLES;|
|postgres@origin:~$ psql "dbname= replication=database"
|
|
|my_database=# CREATE_REPLICATION_SLOT  LOGICAL pgoutput;|
pg_dump -j4 -h  -p 5432 --no-subscriptions --no-publications -d 
 --snapshot= -Fd -U  -f 

postgres@destination:/mnt/database$ pg_restore -d  -j 5 



CREATE SUBSCRIPTION 
        CONNECTION 'host= dbname= user=replica 
password=?? port=5432'

        PUBLICATION 
        WITH (slot_name=, create_slot=false, copy_data=false);
|


After this migration we started to have this kind of problem in both 
replica and primary servers.


`This looks pretty messed up.  Are you sure the debug symbols you're using`
What exactly do you mean? I'm not too familiar with this debug toolings, 
the packages I've used were:


postgresql-14/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
postgresql-14-dbgsym/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]

`Even better, can you construct a self-contained test case?`:
Actually I couldn't reproduce the problem because it's happening just in 
a production database, and it doesn't look to have a pattern in the 
cases when it happens.


Is there anything I could provide you to help the analysis ?



On Mon, Nov 7, 2022 at 3:08 PM Tom Lane > wrote:


Willian Colognesi mailto:willian_cologn...@trimble.com>> writes:
 > I started to use version `14.5-2.pgdg20.04+2` for a dedicated
database and
 > I'm facing many segmentation faults during the day when the
database has
 > more heavy queries.

I take it things were okay with the version you used previously?
What was that exactly?  Has anything else changed?

 > I could also get a little information from gdb, I'm not sure if
it will
 > help:

This looks pretty messed up.  Are you sure the debug symbols you're
using
match the package?

Even better, can you construct a self-contained test case?

                         regards, tom lane



--

*Willian Cezar de O. Colognesi
*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090




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

Re: Segmentation Fault PG 14

[Willian Colognesi]

Hi Tom,

`I take it things were okay with the version you used previously?`
Yes, it was working pretty well in another instance with pg version
`12.4-1.pgdg18.04+1`, and we had to make a migration of one database that
was running in this server to another using Logical Replication.

the process was basically this:
CREATE PUBLICATION my_database_pub FOR ALL TABLES;
postgres@origin:~$ psql "dbname= replication=database"
my_database=# CREATE_REPLICATION_SLOT  LOGICAL pgoutput;
pg_dump -j4 -h  -p 5432 --no-subscriptions --no-publications -d
 --snapshot= -Fd -U  -f

postgres@destination:/mnt/database$ pg_restore -d  -j 5


CREATE SUBSCRIPTION 
   CONNECTION 'host= dbname= user=replica
password=?? port=5432'
   PUBLICATION 
   WITH (slot_name=, create_slot=false, copy_data=false);

After this migration we started to have this kind of problem in both
replica and primary servers.

`This looks pretty messed up.  Are you sure the debug symbols you're using`
What exactly do you mean? I'm not too familiar with this debug toolings,
the packages I've used were:

postgresql-14/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
postgresql-14-dbgsym/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]

`Even better, can you construct a self-contained test case?`:
Actually I couldn't reproduce the problem because it's happening just in a
production database, and it doesn't look to have a pattern in the cases
when it happens.

Is there anything I could provide you to help the analysis ?



On Mon, Nov 7, 2022 at 3:08 PM Tom Lane  wrote:

> Willian Colognesi  writes:
> > I started to use version `14.5-2.pgdg20.04+2` for a dedicated database
> and
> > I'm facing many segmentation faults during the day when the database has
> > more heavy queries.
>
> I take it things were okay with the version you used previously?
> What was that exactly?  Has anything else changed?
>
> > I could also get a little information from gdb, I'm not sure if it will
> > help:
>
> This looks pretty messed up.  Are you sure the debug symbols you're using
> match the package?
>
> Even better, can you construct a self-contained test case?
>
> regards, tom lane
>


-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: copy file from a client app to remote postgres isntance

[Adrian Klaver]

On 11/7/22 8:57 AM, Вадим Самохин wrote:

Hi all,
I have an application that must copy a local file in csv format to a 
postgres table on a remote host. The closest solution is this one 
(https://stackoverflow.com/a/9327519/618020 
). It boils down to 
specifying a \copy meta-command in a psql command:


|psql -U %s -p %s -d %s -f - 

My account was locked in pgadmin4

[William Torrez Corea]

I reset the password but i don't receive any message

-- 

With kindest regards, William.

⢀⣴⠾⠻⢶⣦⠀
⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system
⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org
⠈⠳⣄

Re: Segmentation Fault PG 14

[Tom Lane]

Willian Colognesi  writes:
> I started to use version `14.5-2.pgdg20.04+2` for a dedicated database and
> I'm facing many segmentation faults during the day when the database has
> more heavy queries.

I take it things were okay with the version you used previously?
What was that exactly?  Has anything else changed?

> I could also get a little information from gdb, I'm not sure if it will
> help:

This looks pretty messed up.  Are you sure the debug symbols you're using
match the package?

Even better, can you construct a self-contained test case?

regards, tom lane

Re: copy file from a client app to remote postgres isntance

[Rob Sargent]

On 11/7/22 10:51, Вадим Самохин wrote:
Well, actually, just ordinary 3 tier architecture. Simple UI connected 
via restful API with backend written in php, which copies some data in 
a remote database, that's pretty much it.


пн, 7 нояб. 2022 г. в 20:30, Rob Sargent :

On 11/7/22 09:57, Вадим Самохин wrote:

Hi all,
I have an application that must copy a local file in csv format
to a postgres table on a remote host. The closest solution is
this one (https://stackoverflow.com/a/9327519/618020
). It boils down to
specifying a \copy meta-command in a psql command:
|psql -U %s -p %s -d %s -f - <
There are bulk copy routines available. What is your architecture?


We generally "bottom post" in this group.

Most things I find on the web suggest send the csv file to server and 
run COPY there.  Some show iterating over the csv, but critical to get 
csv off the client.

Re: copy file from a client app to remote postgres isntance

[Вадим Самохин]

Well, actually, just ordinary 3 tier architecture. Simple UI connected via
restful API with backend written in php, which copies some data in a remote
database, that's pretty much it.

пн, 7 нояб. 2022 г. в 20:30, Rob Sargent :

> On 11/7/22 09:57, Вадим Самохин wrote:
>
> Hi all,
> I have an application that must copy a local file in csv format to a
> postgres table on a remote host. The closest solution is this one (https
> ://stackoverflow.com/a/9327519/618020). It boils down to specifying a
> \copy meta-command in a psql command:
>
> psql -U %s -p %s -d %s -f - < executing it. But it's quite an unnatural way to write database code. Has 
> anything changed in the last ten years? Or, is there a better way to copy 
> file contents in a remote database?
>
> There are bulk copy routines available.  What is your architecture?
>

Segmentation Fault PG 14

[Willian Colognesi]

Hello!

I started to use version `14.5-2.pgdg20.04+2` for a dedicated database and
I'm facing many segmentation faults during the day when the database has
more heavy queries.

The server log there are many of this:
```
2022-11-07 17:23:19.423 UTC [728] LOG:  background worker "parallel worker"
(PID 9558) was terminated by signal 11: Segmentation fault
2022-11-07 17:23:19.423 UTC [728] DETAIL:  Failed process was running:
select blablabla from heavyquery where ...;
2022-11-07 17:23:19.423 UTC [728] LOG:  terminating any other active server
processes
2022-11-07 17:23:19.681 UTC [9588] microservice@microservice FATAL:  the
database system is in recovery mode
2022-11-07 17:23:19.683 UTC [9589] microservice@microservice FATAL:  the
database system is in recovery mode
2022-11-07 17:23:24.543 UTC [728] LOG:  all server processes terminated;
reinitializing
2022-11-07 17:23:24.894 UTC [9622] LOG:  database system was interrupted;
last known up at 2022-11-07 17:22:07 UTC
2022-11-07 17:23:25.636 UTC [9622] LOG:  invalid record length at
134/227A3A68: wanted 24, got 0
2022-11-07 17:23:25.636 UTC [9622] LOG:  redo done at 134/227A3A38 system
usage: CPU: user: 0.04 s, system: 0.06 s, elapsed: 0.70 s
2022-11-07 17:23:27.608 UTC [728] LOG:  database system is ready to accept
connections
2022-11-07 17:23:33.474 UTC [9635] replica@[unknown] LOG:  could not
receive data from client: Connection reset by peer
2022-11-07 17:23:33.474 UTC [9635] replica@[unknown] STATEMENT:
 START_REPLICATION 134/2200 TIMELINE 1
2022-11-07 17:23:33.474 UTC [9635] replica@[unknown] LOG:  unexpected EOF
on standby connection
2022-11-07 17:23:33.474 UTC [9635] replica@[unknown] STATEMENT:
 START_REPLICATION 134/2200 TIMELINE 1
2022-11-07 17:23:51.310 UTC [9662] replica@[unknown] LOG:  could not
receive data from client: Connection reset by peer
2022-11-07 17:23:51.310 UTC [9662] replica@[unknown] STATEMENT:
 START_REPLICATION 134/2200 TIMELINE 1
2022-11-07 17:23:51.310 UTC [9662] replica@[unknown] LOG:  unexpected EOF
on standby connection
2022-11-07 17:23:51.310 UTC [9662] replica@[unknown] STATEMENT:
 START_REPLICATION 134/2200 TIMELINE 1
INFO: 2022/11/07 17:23:51.445710 FILE PATH: 000101340022.lz4
2022-11-07 17:24:09.206 UTC [9672] replica@[unknown] LOG:  could not
receive data from client: Connection reset by peer
2022-11-07 17:24:09.206 UTC [9672] replica@[unknown] STATEMENT:
 START_REPLICATION 134/2300 TIMELINE 1
2022-11-07 17:24:09.206 UTC [9672] replica@[unknown] LOG:  unexpected EOF
on standby connection
2022-11-07 17:24:09.206 UTC [9672] replica@[unknown] STATEMENT:
 START_REPLICATION 134/2300 TIMELINE 1
INFO: 2022/11/07 17:24:27.527897 FILE PATH: 000101340023.lz4
INFO: 2022/11/07 17:24:38.076058 FILE PATH: 000101340024.lz4
```

It's server is running in ubuntu 22.04 in aarch64 (ARM architecture)

I could also get a little information from gdb, I'm not sure if it will
help:
```
Type "apropos word" to search for commands related to "word"...
Reading symbols from /usr/lib/postgresql/14/bin/postgres...
Reading symbols from
/usr/lib/debug/.build-id/d7/87a0cf1bb645b349f7c137e36cc30f7ba8805f.debug...
[New LWP 9559]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/aarch64-linux-gnu/libthread_db.so.1".
Core was generated by `postgres: 14/main: parallel worker for PID 9528
  '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x00010c757c9c in ?? ()
(gdb) bt
#0  0x00010c757c9c in ?? ()
#1  0x0c757124 in ?? ()
#2  0xc2ac9970 in ExecProcNode (node=0xfc599818) at
./build/../src/include/executor/executor.h:257
#3  ExecAppend (pstate=0xfc595918) at
./build/../src/backend/executor/nodeAppend.c:360
#4  0xc2ac9970 in ExecProcNode (node=0xfc595918) at
./build/../src/include/executor/executor.h:257
#5  ExecAppend (pstate=0xfc526988) at
./build/../src/backend/executor/nodeAppend.c:360
#6  0x0001 in ?? ()
Backtrace stopped: previous frame identical to this frame (corrupt stack?)
(gdb)
```

Has anyone already faced this problem or may know a solution?

Thanks in advance.

-- 


*Willian Cezar de O. Colognesi*
Systems Analysis Specialist, Trimble Transportation Brazil
Avenida Santos Dumont, 271 | Londrina, PR | 86039-090

Re: copy file from a client app to remote postgres isntance

[Rob Sargent]

On 11/7/22 09:57, Вадим Самохин wrote:

Hi all,
I have an application that must copy a local file in csv format to a 
postgres table on a remote host. The closest solution is this one 
(https://stackoverflow.com/a/9327519/618020 
). It boils down to 
specifying a \copy meta-command in a psql command:
|psql -U %s -p %s -d %s -f - <\nEOT\n ||and executing it. B|ut it's quite an unnatural way to write database 
code. Has anything changed in the last ten years? Or, is there a 
better wayto copy file contents in a remote database?

There are bulk copy routines available. What is your architecture?

copy file from a client app to remote postgres isntance

[Вадим Самохин]

Hi all,
I have an application that must copy a local file in csv format to a
postgres table on a remote host. The closest solution is this one (https://
stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
meta-command in a psql command:

psql -U %s -p %s -d %s -f - <

AW: AW: Reducing bandwidth usage of database replication

[Sascha Zenglein]

Thanks, the wal_sender_timeout setting was the culprit!
A bit hard to find that it influences a ping at half its interval time though.


Sascha Zenglein

Produktentwicklung


[cid:gessler_email_logo_23bb5200-2c8c-4c2d-a63e-71d5bf29d89f.gif]





Gessler GmbH
Gutenbergring 14
63110 Rodgau
Deutschland

Tel.:  +49 6106 8709 693
Fax:  +49 6106 8709 50

E-Mail: zengl...@gessler.de
Web: www.gessler.de 




Geschaeftsfuehrer: Helmut Gessler, Dipl.-Ing. Marcus Gessler
Gerichtsstand: Offenbach/Main, Amtsgericht Offenbach HRB 20857
USt.-IdNr.: DE 113 551 141
Sparkasse Dieburg, IBAN DE94 5085 2651 0057 0025 03, SWIFT HELADEF1DIE


Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich 
erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail
und/oder der Inhalte dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail.
Any unauthorised copying, disclosure or distribution of the material of this 
e-mail is strictly forbidden.

Von: Kyotaro Horiguchi 
Gesendet: Montag, 7. November 2022 06:21
An: Sascha Zenglein 
Cc: simon.ri...@enterprisedb.com ; 
pgsql-general@lists.postgresql.org 
Betreff: Re: AW: Reducing bandwidth usage of database replication

At Fri, 4 Nov 2022 09:25:44 +, Sascha Zenglein  wrote 
in
> I have set the status interval to 600s and it still sends a status message 
> every 30s.
> I also tried setting every available option higher, with no success:

I guess you are not setting wal_sender_timeout on the primary side.

Status messages are also sent in response to sender pings that is
controlled by that variable.  Wal sender sends a ping after a half of
that setting since the last status message to request walreceiver to
send a response.

> checkpoint_warning = 93s
> max_standby_streaming_delay = 130s
> max_standby_archive_delay = 45s
> wal_receiver_status_interval = 600s
> wal_receiver_timeout = 1200s
> wal_receiver_timeout = 65s
> recovery_min_apply_delay = 600s
>
> The random values were to see which setting is limiting if I got above the 
> 30s limit.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Ron]

On 11/7/22 09:43, Tom Lane wrote:

Ron  writes:

On 11/7/22 08:02, Tom Lane wrote:

call. It'd still be recommendable to pg_dumpall and restore into
a freshly-initdb'd cluster, because otherwise you can't be real
sure that you identified and cleared all the data corruption.

Why *just* pg_dumpall instead of "pg_dumpall --globals-only" and parallel
(for speed) dumps of databases?

[ shrug... ] Sure, if you like to make your life complicated.


I don't remember if OP specified the size of his cluster, but Size Matters.  
Dumping a multi-TB database to an SQL text file, and then restoring it will 
be a whole lot slower than doing multi-threaded dump/restore.


--
Angular momentum makes the world go 'round.

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Tom Lane]

Ron  writes:
> On 11/7/22 08:02, Tom Lane wrote:
>> call. It'd still be recommendable to pg_dumpall and restore into
>> a freshly-initdb'd cluster, because otherwise you can't be real
>> sure that you identified and cleared all the data corruption.

> Why *just* pg_dumpall instead of "pg_dumpall --globals-only" and parallel 
> (for speed) dumps of databases?

[ shrug... ] Sure, if you like to make your life complicated.

regards, tom lane

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Ron]

On 11/7/22 08:02, Tom Lane wrote:
[snip]

call. It'd still be recommendable to pg_dumpall and restore into
a freshly-initdb'd cluster, because otherwise you can't be real
sure that you identified and cleared all the data corruption.


Why *just* pg_dumpall instead of "pg_dumpall --globals-only" and parallel 
(for speed) dumps of databases?


--
Angular momentum makes the world go 'round.

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Stefan Froehlich]

On Mon, Nov 07, 2022 at 09:02:26AM -0500, Tom Lane wrote:
> Stefan Froehlich  writes:
> > On Mon, Nov 07, 2022 at 08:17:10AM -0500, Mladen Gogala wrote:
> >> On 11/7/22 06:19, Laurenz Albe wrote:
> >>> Don't continue to work with that cluster even if everything seems OK now.
> >>> "pg_dumpall" and restore to a new cluster on good hardware.
>  
> >> Why would that be necessary if the original machine works well now?
> 
> > I can understand the idea not to trust hardware anymore once a (not
> > clearly identified) problem occured.
> 
> > In this case new hardware would - for reasons beyond the scope of
> > this list - not be any more or less trustworthy than the existing
> > one and thus (IMO) not make any difference.
> 
> Whether you want to continue to trust the hardware or not is your
> call.  It'd still be recommendable to pg_dumpall and restore into
> a freshly-initdb'd cluster, because otherwise you can't be real
> sure that you identified and cleared all the data corruption.

Thanks, yes. This is in fact on my schedule for the next weekend as
it implies a downtime of serveral hours.

Bye,
Stefan

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Tom Lane]

Stefan Froehlich  writes:
> On Mon, Nov 07, 2022 at 08:17:10AM -0500, Mladen Gogala wrote:
>> On 11/7/22 06:19, Laurenz Albe wrote:
>>> Don't continue to work with that cluster even if everything seems OK now.
>>> "pg_dumpall" and restore to a new cluster on good hardware.
 
>> Why would that be necessary if the original machine works well now?

> I can understand the idea not to trust hardware anymore once a (not
> clearly identified) problem occured.

> In this case new hardware would - for reasons beyond the scope of
> this list - not be any more or less trustworthy than the existing
> one and thus (IMO) not make any difference.

Whether you want to continue to trust the hardware or not is your
call.  It'd still be recommendable to pg_dumpall and restore into
a freshly-initdb'd cluster, because otherwise you can't be real
sure that you identified and cleared all the data corruption.

regards, tom lane

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Stefan Froehlich]

On Mon, Nov 07, 2022 at 08:17:10AM -0500, Mladen Gogala wrote:
> On 11/7/22 06:19, Laurenz Albe wrote:
> >Don't continue to work with that cluster even if everything seems OK now.
> >"pg_dumpall" and restore to a new cluster on good hardware.
 
> Why would that be necessary if the original machine works well now?

I can understand the idea not to trust hardware anymore once a (not
clearly identified) problem occured.

In this case new hardware would - for reasons beyond the scope of
this list - not be any more or less trustworthy than the existing
one and thus (IMO) not make any difference.

Bye,
Stefan

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Mladen Gogala]

On 11/7/22 06:19, Laurenz Albe wrote:

Don't continue to work with that cluster even if everything seems OK now.
"pg_dumpall" and restore to a new cluster on good hardware.


Why would that be necessary if the original machine works well now?

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: postgres replication without pg_basebackup? postgres 13.3

[Laurenz Albe]

On Mon, 2022-11-07 at 11:02 +0100, Pilar de Teodoro wrote:
> We have a very large database of 37TB and we had to promote our standby to 
> primary due to
> some disk failures. Now the issues are solved, we would like to make standby  
> the old primary
> from a copy of the new primary which is already in place.  Is it possible 
> without using pg_basebackup?

That's exactly what "pg_rewind" is for.  It is a fast version of 
"pg_basebackup" for exactly
that case.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Laurenz Albe]

On Mon, 2022-11-07 at 11:17 +0100, Stefan Froehlich wrote:
> On Sun, Nov 06, 2022 at 09:48:32AM -0500, Tom Lane wrote:
> > Stefan Froehlich  writes:
> > > > # create extension amcheck;
> > > > # select oid, relname from pg_class where relname 
> > > > ='faultytablename_pkey';
> > > > [returns oid 537203]
> > > > # select bt_index_check(537203, true);
> > > > server closed the connection unexpectedly
> 
> > Another idea is to try using contrib/pageinspect to examine each
> > page of the table.  Its output is just gobbledegook to most
> > people, but there's a good chance it'd fail visibly on the
> > corrupted page(s).
> 
> Fortunately I was able to identify a window of 100 records (out of
> 25 mio.) containing all the errors. After deleting and re-inserting
> those records everything seems to be ok (at least, pg_dump and
> "reindex database" work without errors).

Don't continue to work with that cluster even if everything seems OK now.
"pg_dumpall" and restore to a new cluster on good hardware.

> I suspect a bad RAM module to be the root of the problems. We'll
> see.
> 
> Side question: If it is possible to simply delete and create such
> records is it necessary that the server *core* *dumps*? There could
> be a switch adding additional safety (at the cost of performance)
> which would make troubleshooting not only much faster but
> non-invasive for the other databases on the same server as well.

Crashing is never nice.  On the other hand, adding checks and error
messages for conditions that are always true in a correct block cost
performance.  I can't tell about your specific case, but a build
of PostgreSQL --enable-cassert has assitional checks in the code.
That will still crash, but the log will show what condition was violated.

Yours,
Laurenz Albe

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Stefan Froehlich]

On Sun, Nov 06, 2022 at 09:48:32AM -0500, Tom Lane wrote:
> Stefan Froehlich  writes:
> > | # create extension amcheck;
> > | # select oid, relname from pg_class where relname ='faultytablename_pkey';
> > | [returns oid 537203]
> > | # select bt_index_check(537203, true);
> > | server closed the connection unexpectedly

> Another idea is to try using contrib/pageinspect to examine each
> page of the table.  Its output is just gobbledegook to most
> people, but there's a good chance it'd fail visibly on the
> corrupted page(s).

Fortunately I was able to identify a window of 100 records (out of
25 mio.) containing all the errors. After deleting and re-inserting
those records everything seems to be ok (at least, pg_dump and
"reindex database" work without errors).

I suspect a bad RAM module to be the root of the problems. We'll
see.

Side question: If it is possible to simply delete and create such
records is it necessary that the server *core* *dumps*? There could
be a switch adding additional safety (at the cost of performance)
which would make troubleshooting not only much faster but
non-invasive for the other databases on the same server as well.

Bye,
Stefan

postgres replication without pg_basebackup? postgres 13.3

[Pilar de Teodoro]

Dear all,

We have a very large database of 37TB and we had to promote our standby to
primary due to some disk failures. Now the issues are solved, we would like
to make standby  the old primary  from a copy of the new primary which is
already in place.  Is it possible without using pg_basebackup? We have
already a copy created than can be started but if we open as standby
(standby.signal created) the log says when started:

[2022-10-28 21:27:12 CEST-]LOG:  starting PostgreSQL 13.3 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat
8.5.0-10), 64-bit

[2022-10-28 21:27:12 CEST-]LOG:  listening on IPv4 address "0.0.0.0", port
8300

[2022-10-28 21:27:12 CEST-]LOG:  could not create IPv6 socket for address
"::": Address family not supported by protocol

[2022-10-28 21:27:12 CEST-]LOG:  listening on Unix socket
"/tmp/.s.PGSQL.8300"

[2022-10-28 21:27:12 CEST-]LOG:  database system was interrupted; last
known up at 2022-10-28 20:31:08 CEST

[2022-10-28 21:28:06 CEST-]LOG:  restored log file "0003.history" from
archive

cp: cannot stat '/PostgresWalLogArchive/new/0004.history': No such file
or directory

[2022-10-28 21:28:06 CEST-]LOG:  entering standby mode

[2022-10-28 21:28:06 CEST-]LOG:  restored log file "0003.history" from
archive

cp: cannot stat '/PostgresWalLogArchive/new/0003BF72002F': No
such file or directory

[2022-10-28 21:28:06 CEST-]LOG:  restored log file
"0002BF72002F" from archive

[2022-10-28 21:28:06 CEST-]FATAL:  requested timeline 3 is not a child of
this server's history

*[2022-10-28 21:28:06 CEST-]DETAIL:  Latest checkpoint is at BF72/2F0309C0
on timeline 2, but in the history of the requested timeline, the server
forked off from that timeline at B1D6/6000.*

*[2022-10-28 21:28:06 CEST-]LOG:  startup process (PID 1298266) exited with
exit code 1*

[2022-10-28 21:28:06 CEST-]LOG:  aborting startup due to startup process
failure

[2022-10-28 21:28:07 CEST-]LOG:  database system is shut down





Do you know if we can make it work without having to run pg_basebackup for
37TB which will take about 3 days to copy? Why it is requesting a different
timeline if it is a copy of the primary?


Primary replica parameters:



wal_level = replica

archive_mode = on

archive_command = 'cp -i %p /PostgresWalLogArchive/new/%f'



standby replica parameters:


primary_conninfo = 'host=XXX port=8300 user=postgres password=***'

restore_command = 'cp /PostgresWalLogArchive/new/%f %p'

archive_cleanup_command =
'/home/postgres/software/postgresql/bin/pg_archivecleanup
/PostgresWalLogArchive/new/%f %r'



Thank you very much for your help in advance.


Pilar de Teodoro