Re: psql dones't reflect exit status if input command via stdin

[magodo]

On Mon, 2019-05-13 at 23:39 -0700, David G. Johnston wrote:
> On Mon, May 13, 2019 at 11:24 PM magodo  wrote:
> > I found when running command like `# echo "xxx" | psql postgres
> > 
> > postgres`, the return code is always 0 even though the command
> > ("xxx")
> > 
> > here is of invalid syntax. While the `psql -c` way handled exit
> > code
> > 
> > correctly.
> 
> Its only required to handle things as documented, which this is:
> 
> "psql returns 0 to the shell if it finished normally, 1 if a fatal
> error of its own occurs (e.g. out of memory, file not found), 2 if
> the connection to the server went bad and the session was not
> interactive, and 3 if an error occurred in a script and the variable
> ON_ERROR_STOP was set."
>  
> The pipe-version causes psql to execute stdin as a script.  By
> default ON_ERROR_STOP is unset.  Thus psql finished processing the
> script normally and while it encountered an error it continued past
> the error as opposed to stopping with exit code 3.
> 
> Adding "\set ON_ERROR_STOP 1" to your .psqlrc will result in psql
> behaving in the way you expect.
> 
> David J.

Hi David,

Thank you for your quick and excellent answer 😊

---
Zhaoting.Weng

Re: perl path issue

[Prakash Ramakrishnan]

Hi Adrian,

Yes correct am trying to restore the single database using pgbackrest
command in dev.

Note - if am taking same prod single database backup and restore in new
cluster no use for us and it will take more time.
so business and team they need every 3 weeks for restore in dev server one
single database and cant we do it in pg_dump and restore .
They want using pgbackrest tool the db size is huge so tats why am trying
remote restore option.


dev pgbackrest.conf file

==> cat /etc/pgbackrest.conf
#[global]
#repo1-path=/Postgres/pgBACKUP/A4_sydcosausd001
#retention-diff=4
#retention-full=4
#retention-archive=2
#start-fast=y
#process-max=4
#archive-async=y


#[global:archive-push]
#process-max=4

#[A4_sydcosausd001]
#db-path=/Postgres/pgDATA/data


[global]
repo1-host=sydcosafpp001.enterprisenet.org
repo1-host-user=postgres
repo1-host-config=/etc/pgbackrest.conf
repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001

[A4_sydcosafpp001]
pg1-path=/Postgres/prakash_pgbackrest




On Mon, May 13, 2019 at 10:54 PM Adrian Klaver 
wrote:

> On 5/13/19 8:14 AM, Prakash Ramakrishnan wrote:
> > Hi Ravi,
> >
> > I tried manually export the lib path multiple times not working.
> >
>
> > ==> pgbackrest --stanza=A4_sydcosafpp001 --log-level-console=info
> > --db-path=/Postgres/prakash_pgbackrest --delta  --process-max=2
> > --db-include=A4_Copy restore
> > --repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001
> >
> > 2019-05-13 19:55:05.954 P00   INFO: restore command begin
> > 2.13: --db-include=A4_Copy --delta --log-level-console=info
> > --pg1-path=/Postgres/prakash_pgbackrest --process-max=2
> > --repo1-host=sydcosafpp001.enterprisenet.org
> > 
> > --repo1-host-config=/etc/pgbackrest.conf --repo1-host-user=postgres
> > --repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001
> > --stanza=A4_sydcosafpp001
> > Password:
> > ERROR [124]: : remote process on 'sydcosafpp001.enterprisenet.org
> > ' terminated unexpectedly
> [124]:
>
> Trying to figure out above:
>
> sydcosausd001.enterprisenet.org is your dev
>
> sydcosafpp001.enterprisenet.org  is your prod
>
> Correct?
>
> You are trying to run the pgBackRest on the dev machine, so why is there
> a remote process failure on the prod?
>
>
> > ERROR: [124]: process terminated due to an unhandled exception:
> > *  Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for
> > module DBD::Pg: libpq.so.5: cannot open shared object file: No such file
> > or directory at /usr/lib64/perl5/DynaLoader.pm line 190.*
> > at /loader/0xc54528/pgBackRest/Db.pm line 10.
> > at /loader/0xc54528/pgBackRest/Main.pm line 12.
> > pgBackRest::Main::__ANON__('Can\'t load
> > \'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at
> > /usr/share/perl5/vendor_perl/Carp.pm line 100
> >
> >
> > Regards,
> > Prakash.R
> >
> > On Mon, May 13, 2019 at 8:39 PM Ravi Krishna  > > wrote:
> >
> >  >
> >  > ==> ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
> >  > linux-vdso.so.1 =>  (0x7fffddd8f000)
> >  > libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5
> > (0x7f5ecdbd6000)
> >  
> >
> > I may be wrong, but is the above path in the LD_LIBRARY_PATH. Why do
> > you expect it to be picked auto magically.
> >
> >
> >
> >
> > --
> >
> >
> >
> > Thanks,
> > Prakash.R
> > PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
> > call : +91-8939599426
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: perl path issue

[Ravi Krishna]

> 
> Note - if am taking same prod single database backup and restore in new 
> cluster no use for us and it will take more time.
> so business and team they need every 3 weeks for restore in dev server one 
> single database and cant we do it in pg_dump and restore .
> They want using pgbackrest tool the db size is huge so tats why am trying 
> remote restore option.

I am baffled.  Are you telling that you are restoring it back on prod (using 
remote restore option), 
which effectively means overwriting prod db.  Also you never gave this 
information until now. You
should have shared full details. 

I have not used pgbackest, but I have read the FAQ. If I am not mistaken it has 
single db restore option too. So you can restore
the db in dev.

Re: perl path issue

[Prakash Ramakrishnan]

Hi Ravi,

Not , am saying we have the daily backup and full backup in prod server
only and there is one database like a4 the db size is 1.5TB.
so am not restore again in prod .
Am taking directly single backup restore in dev its means in dev server
only restore the database in new cluster.
existing cluster we cant restore backups.
so thats why am struggling the issue getting some perl issue and remote
option terminated otherwise wont disturb anyone and need to solve this
issue.
this activity has been planned every 3 weeks.

Regards,
Prakash.R

On Tue, May 14, 2019 at 3:10 PM Ravi Krishna  wrote:

> >
> > Note - if am taking same prod single database backup and restore in new
> cluster no use for us and it will take more time.
> > so business and team they need every 3 weeks for restore in dev server
> one single database and cant we do it in pg_dump and restore .
> > They want using pgbackrest tool the db size is huge so tats why am
> trying remote restore option.
>
> I am baffled.  Are you telling that you are restoring it back on prod
> (using remote restore option),
> which effectively means overwriting prod db.  Also you never gave this
> information until now. You
> should have shared full details.
>
> I have not used pgbackest, but I have read the FAQ. If I am not mistaken
> it has single db restore option too. So you can restore
> the db in dev.
>
>
>

-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: perl path issue

[Ravi Krishna]

> 
> 
> Not , am saying we have the daily backup and full backup in prod server only 
> and there is one database like a4 the db size is 1.5TB.
> so am not restore again in prod .
> Am taking directly single backup restore in dev its means in dev server only 
> restore the database in new cluster.
> existing cluster we cant restore backups.
> so thats why am struggling the issue getting some perl issue and remote 
> option terminated otherwise wont disturb anyone and need to solve this issue.
> this activity has been planned every 3 weeks.
> 

Sorry I am not able to follow the above. Did anyone else ?

Re: perl path issue

[Rob Sargent]

Which part confused you Ravi?
Here’s my take
> On May 14, 2019, at 4:06 AM, Ravi Krishna  wrote:
> 
>> 
>> 
>> Not , am saying we have the daily backup and full backup in prod server only 
>> and there is one database like a4 the db size is 1.5TB.
>> so am not restore again in prod .
Production server is fine and generating a new back-up daily.  (Presumably with 
pgBackrest?)

>> Am taking directly single backup restore in dev its means in dev server only 
>> restore the database in new cluster.
>> existing cluster we cant restore backups.
This is the part that I might not understand: a new postgres instance on the 
same dev server? both old and new failing to restore at this time? 
Does this imply that dev is the source of the dump being restored (as opposed 
to using the daily from prod)?

>> so thats why am struggling the issue getting some perl issue and remote 
>> option terminated otherwise wont disturb anyone and need to solve this issue.
>> this activity has been planned every 3 weeks.
>> 
The hope is to reload dev every three weeks.
> 
> Sorry I am not able to follow the above. Did anyone else ?
> 
> 
> 

I wonder if rebuilding dev server (or a different server) from the ground up is 
an option with all PG related parts install as per prod?

Re: perl path issue

[Ravi Krishna]

---  Original Message  
--

> On May 14, 2019, at 9:06 AM, Rob Sargent  wrote:
> 
> 
> Which part confused you Ravi?

Same as you, this one
"Am taking directly single backup restore in dev its means in dev server only 
restore the database in new cluster existing cluster we cant restore backups."

I don't want to second guess and I would prefer all information supplied in one 
post, rather than in installments.

Re: perl path issue

[Adrian Klaver]

On 5/14/19 2:51 AM, Prakash Ramakrishnan wrote:

Hi Ravi,

Not , am saying we have the daily backup and full backup in prod server 
only and there is one database like a4 the db size is 1.5TB.

so am not restore again in prod .
Am taking directly single backup restore in dev its means in dev server 
only restore the database in new cluster.

existing cluster we cant restore backups.
so thats why am struggling the issue getting some perl issue and remote 
option terminated otherwise wont disturb anyone and need to solve this 
issue.

this activity has been planned every 3 weeks.


I think to make this clearer for everyone a graphical layout might help. 
Something like:


prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily

dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks

Of course the above is just made up.


What I think we know so far, please check and correct as necessary:

1) Prod server CentOS 7, Postgres 10 from EDB installer, pgBackRest from 
PGDG repo


2) Dev server CentOS 7, Postgres 10 from EDB installer, pgBackRest from 
PGDG repo


3) sydcosausd001.enterprisenet.org is your dev

4) sydcosafpp001.enterprisenet.org  is your prod


5) pgBackRest works on prod server.

6) pgBackRest fails on dev server with:

Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for module 
DBD::Pg: libpq.so.5: cannot open shared object file: No such file or 
directory at /usr/lib64/perl5/DynaLoader.pm line 190.


7) ldd of Pg.so. From dev correct?:

 ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
linux-vdso.so.1 =>  (0x7fffddd8f000)
libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5 (0x7f5ecdbd6000)
libm.so.6 => /lib64/libm.so.6 (0x7f5ecd8d4000)
libc.so.6 => /lib64/libc.so.6 (0x7f5ecd507000)
libssl.so.1.0.0 => /usr/lib64/perl5/CORE/libssl.so.1.0.0 
(0x7f5ecd297000)
libcrypto.so.1.0.0 => /usr/lib64/perl5/CORE/libcrypto.so.1.0.0 
(0x7f5ecce5d000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 
(0x7f5eccc1)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 
(0x7f5ecc9b1000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x7f5ecc795000)
/lib64/ld-linux-x86-64.so.2 (0x7f5ece056000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f5ecc591000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f5ecc2a8000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f5ecc075000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f5ecbe71000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 
(0x7f5ecbc61000)

libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f5ecba5d000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x7f5ecb844000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f5ecb635000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f5ecb418000)
libssl.so.10 => /lib64/libssl.so.10 (0x7f5ecb1a6000)
libcrypto.so.10 => /lib64/libcrypto.so.10 (0x7f5ecad45000)
libssl3.so => /lib64/libssl3.so (0x7f5ecaaf3000)
libsmime3.so => /lib64/libsmime3.so (0x7f5eca8cc000)
libnss3.so => /lib64/libnss3.so (0x7f5eca59f000)
libnssutil3.so => /lib64/libnssutil3.so (0x7f5eca37)
libplds4.so => /lib64/libplds4.so (0x7f5eca16c000)
libplc4.so => /lib64/libplc4.so (0x7f5ec9f67000)
libnspr4.so => /lib64/libnspr4.so (0x7f5ec9d29000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x7f5ec9b02000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f5ec98cb000)
libz.so.1 => /lib64/libz.so.1 (0x7f5ec96b5000)
librt.so.1 => /lib64/librt.so.1 (0x7f5ec94ad000)
libpcre.so.1 => /lib64/libpcre.so.1 (0x7f5ec924b000)
libfreebl3.so => /lib64/libfreebl3.so (0x7f5ec9048000)
postg...@sydcosausd001.enterprisenet.org:/home/postgres


Regards,
Prakash.R

On Tue, May 14, 2019 at 3:10 PM Ravi Krishna > wrote:


 >
 > Note - if am taking same prod single database backup and restore
in new cluster no use for us and it will take more time.
 > so business and team they need every 3 weeks for restore in dev
server one single database and cant we do it in pg_dump and restore .
 > They want using pgbackrest tool the db size is huge so tats why
am trying remote restore option.

I am baffled.  Are you telling that you are restoring it back on
prod (using remote restore option),
which effectively means overwriting prod db.  Also you never gave
this information until now. You
should have shared full details.

I have not used pgbackest, but I have read the FAQ. If I am not
mistaken it has single db restore option too. So you can restore
the db in dev.




--



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On 
call : +91-8939599426



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

Re: perl path issue

[Prakash Ramakrishnan]

Yes, that is correct.

On Tue, May 14, 2019 at 7:54 PM Adrian Klaver 
wrote:

> On 5/14/19 2:51 AM, Prakash Ramakrishnan wrote:
> > Hi Ravi,
> >
> > Not , am saying we have the daily backup and full backup in prod server
> > only and there is one database like a4 the db size is 1.5TB.
> > so am not restore again in prod .
> > Am taking directly single backup restore in dev its means in dev server
> > only restore the database in new cluster.
> > existing cluster we cant restore backups.
> > so thats why am struggling the issue getting some perl issue and remote
> > option terminated otherwise wont disturb anyone and need to solve this
> > issue.
> > this activity has been planned every 3 weeks.
>
> I think to make this clearer for everyone a graphical layout might help.
> Something like:
>
> prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily
>
> dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks
>
> Of course the above is just made up.
>
>
> What I think we know so far, please check and correct as necessary:
>
> 1) Prod server CentOS 7, Postgres 10 from EDB installer, pgBackRest from
> PGDG repo
>
> 2) Dev server CentOS 7, Postgres 10 from EDB installer, pgBackRest from
> PGDG repo
>
> 3) sydcosausd001.enterprisenet.org is your dev
>
> 4) sydcosafpp001.enterprisenet.org  is your prod
>
>
> 5) pgBackRest works on prod server.
>
> 6) pgBackRest fails on dev server with:
>
> Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for module
> DBD::Pg: libpq.so.5: cannot open shared object file: No such file or
> directory at /usr/lib64/perl5/DynaLoader.pm line 190.
>
> 7) ldd of Pg.so. From dev correct?:
>
>   ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
>  linux-vdso.so.1 =>  (0x7fffddd8f000)
>  libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5
> (0x7f5ecdbd6000)
>  libm.so.6 => /lib64/libm.so.6 (0x7f5ecd8d4000)
>  libc.so.6 => /lib64/libc.so.6 (0x7f5ecd507000)
>  libssl.so.1.0.0 => /usr/lib64/perl5/CORE/libssl.so.1.0.0
> (0x7f5ecd297000)
>  libcrypto.so.1.0.0 => /usr/lib64/perl5/CORE/libcrypto.so.1.0.0
> (0x7f5ecce5d000)
>  libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2
> (0x7f5eccc1)
>  libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2
> (0x7f5ecc9b1000)
>  libpthread.so.0 => /lib64/libpthread.so.0 (0x7f5ecc795000)
>  /lib64/ld-linux-x86-64.so.2 (0x7f5ece056000)
>  libdl.so.2 => /lib64/libdl.so.2 (0x7f5ecc591000)
>  libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f5ecc2a8000)
>  libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f5ecc075000)
>  libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f5ecbe71000)
>  libkrb5support.so.0 => /lib64/libkrb5support.so.0
> (0x7f5ecbc61000)
>  libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f5ecba5d000)
>  libresolv.so.2 => /lib64/libresolv.so.2 (0x7f5ecb844000)
>  liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f5ecb635000)
>  libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f5ecb418000)
>  libssl.so.10 => /lib64/libssl.so.10 (0x7f5ecb1a6000)
>  libcrypto.so.10 => /lib64/libcrypto.so.10 (0x7f5ecad45000)
>  libssl3.so => /lib64/libssl3.so (0x7f5ecaaf3000)
>  libsmime3.so => /lib64/libsmime3.so (0x7f5eca8cc000)
>  libnss3.so => /lib64/libnss3.so (0x7f5eca59f000)
>  libnssutil3.so => /lib64/libnssutil3.so (0x7f5eca37)
>  libplds4.so => /lib64/libplds4.so (0x7f5eca16c000)
>  libplc4.so => /lib64/libplc4.so (0x7f5ec9f67000)
>  libnspr4.so => /lib64/libnspr4.so (0x7f5ec9d29000)
>  libselinux.so.1 => /lib64/libselinux.so.1 (0x7f5ec9b02000)
>  libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f5ec98cb000)
>  libz.so.1 => /lib64/libz.so.1 (0x7f5ec96b5000)
>  librt.so.1 => /lib64/librt.so.1 (0x7f5ec94ad000)
>  libpcre.so.1 => /lib64/libpcre.so.1 (0x7f5ec924b000)
>  libfreebl3.so => /lib64/libfreebl3.so (0x7f5ec9048000)
> postg...@sydcosausd001.enterprisenet.org:/home/postgres
> >
> > Regards,
> > Prakash.R
> >
> > On Tue, May 14, 2019 at 3:10 PM Ravi Krishna  > > wrote:
> >
> >  >
> >  > Note - if am taking same prod single database backup and restore
> > in new cluster no use for us and it will take more time.
> >  > so business and team they need every 3 weeks for restore in dev
> > server one single database and cant we do it in pg_dump and restore .
> >  > They want using pgbackrest tool the db size is huge so tats why
> > am trying remote restore option.
> >
> > I am baffled.  Are you telling that you are restoring it back on
> > prod (using remote restore option),
> > which effectively means overwriting prod db.  Also you never gave
> > this information until now. You
> > should have

Re: perl path issue

[Adrian Klaver]

On 5/14/19 7:28 AM, Prakash Ramakrishnan wrote:

Yes, that is correct.


How about graphical layout of your backup/restore procedure?



On Tue, May 14, 2019 at 7:54 PM Adrian Klaver > wrote:


On 5/14/19 2:51 AM, Prakash Ramakrishnan wrote:
 > Hi Ravi,
 >
 > Not , am saying we have the daily backup and full backup in prod
server
 > only and there is one database like a4 the db size is 1.5TB.
 > so am not restore again in prod .
 > Am taking directly single backup restore in dev its means in dev
server
 > only restore the database in new cluster.
 > existing cluster we cant restore backups.
 > so thats why am struggling the issue getting some perl issue and
remote
 > option terminated otherwise wont disturb anyone and need to solve
this
 > issue.
 > this activity has been planned every 3 weeks.

I think to make this clearer for everyone a graphical layout might
help.
Something like:

prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily

dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks

Of course the above is just made up.


What I think we know so far, please check and correct as necessary:

1) Prod server CentOS 7, Postgres 10 from EDB installer, pgBackRest
from
PGDG repo

2) Dev server CentOS 7, Postgres 10 from EDB installer, pgBackRest from
PGDG repo

3) sydcosausd001.enterprisenet.org
 is your dev

4) sydcosafpp001.enterprisenet.org
  is your prod


5) pgBackRest works on prod server.

6) pgBackRest fails on dev server with:

Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for module
DBD::Pg: libpq.so.5: cannot open shared object file: No such file or
directory at /usr/lib64/perl5/DynaLoader.pm line 190.

7) ldd of Pg.so. From dev correct?:

   ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
          linux-vdso.so.1 =>  (0x7fffddd8f000)
          libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5
(0x7f5ecdbd6000)
          libm.so.6 => /lib64/libm.so.6 (0x7f5ecd8d4000)
          libc.so.6 => /lib64/libc.so.6 (0x7f5ecd507000)
          libssl.so.1.0.0 => /usr/lib64/perl5/CORE/libssl.so.1.0.0
(0x7f5ecd297000)
          libcrypto.so.1.0.0 =>
/usr/lib64/perl5/CORE/libcrypto.so.1.0.0
(0x7f5ecce5d000)
          libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2
(0x7f5eccc1)
          libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2
(0x7f5ecc9b1000)
          libpthread.so.0 => /lib64/libpthread.so.0 (0x7f5ecc795000)
          /lib64/ld-linux-x86-64.so.2 (0x7f5ece056000)
          libdl.so.2 => /lib64/libdl.so.2 (0x7f5ecc591000)
          libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f5ecc2a8000)
          libk5crypto.so.3 => /lib64/libk5crypto.so.3
(0x7f5ecc075000)
          libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f5ecbe71000)
          libkrb5support.so.0 => /lib64/libkrb5support.so.0
(0x7f5ecbc61000)
          libkeyutils.so.1 => /lib64/libkeyutils.so.1
(0x7f5ecba5d000)
          libresolv.so.2 => /lib64/libresolv.so.2 (0x7f5ecb844000)
          liblber-2.4.so.2 => /lib64/liblber-2.4.so.2
(0x7f5ecb635000)
          libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f5ecb418000)
          libssl.so.10 => /lib64/libssl.so.10 (0x7f5ecb1a6000)
          libcrypto.so.10 => /lib64/libcrypto.so.10 (0x7f5ecad45000)
          libssl3.so => /lib64/libssl3.so (0x7f5ecaaf3000)
          libsmime3.so => /lib64/libsmime3.so (0x7f5eca8cc000)
          libnss3.so => /lib64/libnss3.so (0x7f5eca59f000)
          libnssutil3.so => /lib64/libnssutil3.so (0x7f5eca37)
          libplds4.so => /lib64/libplds4.so (0x7f5eca16c000)
          libplc4.so => /lib64/libplc4.so (0x7f5ec9f67000)
          libnspr4.so => /lib64/libnspr4.so (0x7f5ec9d29000)
          libselinux.so.1 => /lib64/libselinux.so.1 (0x7f5ec9b02000)
          libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f5ec98cb000)
          libz.so.1 => /lib64/libz.so.1 (0x7f5ec96b5000)
          librt.so.1 => /lib64/librt.so.1 (0x7f5ec94ad000)
          libpcre.so.1 => /lib64/libpcre.so.1 (0x7f5ec924b000)
          libfreebl3.so => /lib64/libfreebl3.so (0x7f5ec9048000)
postg...@sydcosausd001.enterprisenet.org:/home/postgres
 >
 > Regards,
 > Prakash.R
 >
 > On Tue, May 14, 2019 at 3:10 PM Ravi Krishna
mailto:ravi_kris...@aol.com>
 > >> wrote:
 >
 >      >
 >      > Note - if am taking same prod single database backup and
restore
 >     in new cluster no use fo

Re: perl path issue

[Prakash Ramakrishnan]

Hi Adrian,

Prod server retention periods ,

[global]
repo1-path=/pgBACKUP/A4_sydcosafpp001
*retention-diff=3*
*retention-full=1*
*retention-archive=2*
start-fast=y
process-max=12
archive-async=y

Dev server currently we dont have only restore need to do prod to dev.

Regards,
Prakash.R


On Tue, May 14, 2019 at 8:01 PM Adrian Klaver 
wrote:

> On 5/14/19 7:28 AM, Prakash Ramakrishnan wrote:
> > Yes, that is correct.
>
> How about graphical layout of your backup/restore procedure?
>
> >
> > On Tue, May 14, 2019 at 7:54 PM Adrian Klaver  > > wrote:
> >
> > On 5/14/19 2:51 AM, Prakash Ramakrishnan wrote:
> >  > Hi Ravi,
> >  >
> >  > Not , am saying we have the daily backup and full backup in prod
> > server
> >  > only and there is one database like a4 the db size is 1.5TB.
> >  > so am not restore again in prod .
> >  > Am taking directly single backup restore in dev its means in dev
> > server
> >  > only restore the database in new cluster.
> >  > existing cluster we cant restore backups.
> >  > so thats why am struggling the issue getting some perl issue and
> > remote
> >  > option terminated otherwise wont disturb anyone and need to solve
> > this
> >  > issue.
> >  > this activity has been planned every 3 weeks.
> >
> > I think to make this clearer for everyone a graphical layout might
> > help.
> > Something like:
> >
> > prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily
> >
> > dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks
> >
> > Of course the above is just made up.
> >
> >
> > What I think we know so far, please check and correct as necessary:
> >
> > 1) Prod server CentOS 7, Postgres 10 from EDB installer, pgBackRest
> > from
> > PGDG repo
> >
> > 2) Dev server CentOS 7, Postgres 10 from EDB installer, pgBackRest
> from
> > PGDG repo
> >
> > 3) sydcosausd001.enterprisenet.org
> >  is your dev
> >
> > 4) sydcosafpp001.enterprisenet.org
> >   is your prod
> >
> >
> > 5) pgBackRest works on prod server.
> >
> > 6) pgBackRest fails on dev server with:
> >
> > Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for
> module
> > DBD::Pg: libpq.so.5: cannot open shared object file: No such file or
> > directory at /usr/lib64/perl5/DynaLoader.pm line 190.
> >
> > 7) ldd of Pg.so. From dev correct?:
> >
> >ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
> >   linux-vdso.so.1 =>  (0x7fffddd8f000)
> >   libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5
> > (0x7f5ecdbd6000)
> >   libm.so.6 => /lib64/libm.so.6 (0x7f5ecd8d4000)
> >   libc.so.6 => /lib64/libc.so.6 (0x7f5ecd507000)
> >   libssl.so.1.0.0 => /usr/lib64/perl5/CORE/libssl.so.1.0.0
> > (0x7f5ecd297000)
> >   libcrypto.so.1.0.0 =>
> > /usr/lib64/perl5/CORE/libcrypto.so.1.0.0
> > (0x7f5ecce5d000)
> >   libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2
> > (0x7f5eccc1)
> >   libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2
> > (0x7f5ecc9b1000)
> >   libpthread.so.0 => /lib64/libpthread.so.0
> (0x7f5ecc795000)
> >   /lib64/ld-linux-x86-64.so.2 (0x7f5ece056000)
> >   libdl.so.2 => /lib64/libdl.so.2 (0x7f5ecc591000)
> >   libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f5ecc2a8000)
> >   libk5crypto.so.3 => /lib64/libk5crypto.so.3
> > (0x7f5ecc075000)
> >   libcom_err.so.2 => /lib64/libcom_err.so.2
> (0x7f5ecbe71000)
> >   libkrb5support.so.0 => /lib64/libkrb5support.so.0
> > (0x7f5ecbc61000)
> >   libkeyutils.so.1 => /lib64/libkeyutils.so.1
> > (0x7f5ecba5d000)
> >   libresolv.so.2 => /lib64/libresolv.so.2
> (0x7f5ecb844000)
> >   liblber-2.4.so.2 => /lib64/liblber-2.4.so.2
> > (0x7f5ecb635000)
> >   libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f5ecb418000)
> >   libssl.so.10 => /lib64/libssl.so.10 (0x7f5ecb1a6000)
> >   libcrypto.so.10 => /lib64/libcrypto.so.10
> (0x7f5ecad45000)
> >   libssl3.so => /lib64/libssl3.so (0x7f5ecaaf3000)
> >   libsmime3.so => /lib64/libsmime3.so (0x7f5eca8cc000)
> >   libnss3.so => /lib64/libnss3.so (0x7f5eca59f000)
> >   libnssutil3.so => /lib64/libnssutil3.so
> (0x7f5eca37)
> >   libplds4.so => /lib64/libplds4.so (0x7f5eca16c000)
> >   libplc4.so => /lib64/libplc4.so (0x7f5ec9f67000)
> >   libnspr4.so => /lib64/libnspr4.so (0x7f5ec9d29000)
> >   libselinux.so.1 => /lib64/libselinux.so.1
> (0x7f5ec9b02000)
> > 

Re: perl path issue

[Adrian Klaver]

On 5/14/19 7:40 AM, Prakash Ramakrishnan wrote:

Hi Adrian,

Prod server retention periods ,

[global]
repo1-path=/pgBACKUP/A4_sydcosafpp001
*retention-diff=3*
*retention-full=1*
*retention-archive=2*
start-fast=y
process-max=12
archive-async=y

Dev server currently we dont have only restore need to do prod to dev.


The issue is we are having trouble figuring out how the backups are 
getting from one location to another and in what order. So could you 
layout that out like I showed earlier:


prod Pg db --pgBackRest--> some_host/some_dir/some_file  @daily

dev Pg db <--pgBackRest -- some_host/some_dir/some_file  @3 weeks



Regards,
Prakash.R




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

Re: terminating walsender process due to replication timeout

[Rene Romero Benavides]

To detect network issues maybe you could monitor replication delay.

On Mon, May 13, 2019 at 6:42 AM  wrote:

> Hello PostgreSQL Community!
>
> I faced an issue on my linux machine using Postgres 11.3 .
> I have 2 nodes in db cluster: master and standby.
> I tried to perform a plenty of long-running  queries which lead to the
> databases desynchronization:
> terminating walsender process due to replication timeout
>
> Here is the output in debug mode:
> 2019-05-13 13:21:33 FET 0 DEBUG:  sending replication keepalive
> 2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed;
> blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed;
> blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed;
> blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed;
> blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed;
> blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed;
> blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed;
> blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed;
> blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed;
> blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed;
> blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed;
> blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed;
> blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
> 2019-05-13 13:21:34 FET 0 LOG:  terminating walsender process due to
> replication timeout
>
>
> The issue is reproducible. I configure 2 nodes cluster, download
> demo_small.zip from https://edu.postgrespro.ru/ and run the following
> command:
> psql -U user1 -f demo_small.sql db1
> and I get the observed behaviour.
>
>
> I know that I can increase wal_sender_timeout value to avoid this
> behaviour (currently wal_sender_timeout is equal to 1 second.)
> To be honest I don't want to increase wal_sender_timeout because I would
> like to detect some network issues quickly.
>
> After having googled I found that someone faced a similar issue
> https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
> which was fixed in  PostgreSQL 9.4.16.
>
>
> Is my issue the same as described here
> https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
> ?
> Is there any  other chance to avoid it without increasing
> wal_sender_timeout?
>
>
> Thank you in advance.
> Regards,
> Andrei



-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Table update: restore or replace?

[Rich Shepard]

I just updated a column in a table (postgresql-10.5) and accidently applied
it to all 185 rows rather than just one. There is no active transaction to
roll back.

Each night I do a backup and just started working on the database a few
minutes ago. If I restore all contents of
/var/lib/pgsql/10/data/ will this restore the database's state before the
botched update and two previous updates today?

Rich

Re: Table update: restore or replace?

[Rich Shepard]

On Tue, 14 May 2019, Adrian Klaver wrote:


A file level backup or database dump?


Adrian,

File level. April 20th is my most recent database dump because I forgot to
run it last Friday afternoon.

Rich

Re: Table update: restore or replace?

[Adrian Klaver]

On 5/14/19 1:59 PM, Rich Shepard wrote:

On Tue, 14 May 2019, Adrian Klaver wrote:


A file level backup or database dump?


Adrian,

File level. April 20th is my most recent database dump because I forgot to
run it last Friday afternoon.


Does the table you overwrote the data change much?

If not it might be safer to just fetch it from the April 20th dump and 
then apply the changes since then.



If you where to restore at the file level I would:

1) Stop the server.

2) Move the existing content of /var/lib/pgsql/10/data/ somewhere else 
just to be safe.


3) Copy in the backup.





Rich






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

Re: Table update: restore or replace?

[Adrian Klaver]

On 5/14/19 1:46 PM, Rich Shepard wrote:

I just updated a column in a table (postgresql-10.5) and accidently applied
it to all 185 rows rather than just one. There is no active transaction to
roll back.

Each night I do a backup and just started working on the database a few


A file level backup or database dump?


minutes ago. If I restore all contents of
/var/lib/pgsql/10/data/ will this restore the database's state before the
botched update and two previous updates today?

Rich






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

Re: Table update: restore or replace?

[Tom Lane]

Adrian Klaver  writes:
> If you where to restore at the file level I would:
> 1) Stop the server.
> 2) Move the existing content of /var/lib/pgsql/10/data/ somewhere else 
> just to be safe.
> 3) Copy in the backup.

Yeah.  You can't just selectively copy files, because the data files
are dependent on the contents of the pg_xact transaction log; it's
all or nothing.

Also, I don't know what method you've been using to make file-level
backups, but they're really pretty worthless unless you (a) stop
the server or (b) use a filesystem snapshot.  Otherwise you're very
likely to have inconsistent data.

regards, tom lane

Re: Table update: restore or replace?

[Rich Shepard]

On Tue, 14 May 2019, Adrian Klaver wrote:


Does the table you overwrote the data change much?


Adrian,

Yes. It's in my business tracking database so it's updated almost every day.


If not it might be safer to just fetch it from the April 20th dump and
then apply the changes since then.


The column I FUBAR'd holds e-mail addresses. What I'll do is set all to NULL
and find the e-mail addresses I have external to the database and enter them
in a large UPDATE TABLE.

And I think I'll set up a cron job to do a database dump each day with the
date appended to the file name in the bash shell script.

Thanks,

Rich

Re: Table update: restore or replace?

[Rich Shepard]

On Tue, 14 May 2019, Tom Lane wrote:


Yeah. You can't just selectively copy files, because the data files are
dependent on the contents of the pg_xact transaction log; it's all or
nothing.


Tom,

That's why I thought of copying the entire data/ directory.


Also, I don't know what method you've been using to make file-level
backups, but they're really pretty worthless unless you (a) stop the
server or (b) use a filesystem snapshot. Otherwise you're very likely to
have inconsistent data.


I run dirvish  which runs each night starting at
00:30 am when there's no activity (by me, at least) on the database.

Regards,

Rich

Re: Re: Table update: restore or replace?

[Brad Nicholson]

> From: Rich Shepard 
> To: pgsql-general@lists.postgresql.org
> Date: 05/14/2019 05:19 PM
> Subject: [EXTERNAL] Re: Table update: restore or replace?

> And I think I'll set up a cron job to do a database dump each day with
the
> date appended to the file name in the bash shell script.

Might I suggest you setup proper backups with continuous archiving instead?
If you had those, you would be able to restore this database back to the
point right before you issued the bad update statement.

I'd highly recommend pgBackRest for the task.

Brad.

Re: Table update: restore or replace?

[Adrian Klaver]

On 5/14/19 2:19 PM, Rich Shepard wrote:

On Tue, 14 May 2019, Adrian Klaver wrote:


Does the table you overwrote the data change much?


Adrian,

Yes. It's in my business tracking database so it's updated almost every 
day.



If not it might be safer to just fetch it from the April 20th dump and
then apply the changes since then.


The column I FUBAR'd holds e-mail addresses. What I'll do is set all to 
NULL
and find the e-mail addresses I have external to the database and enter 
them

in a large UPDATE TABLE.


Or just fetch them from the table data you have saved in the dump file.



And I think I'll set up a cron job to do a database dump each day with the
date appended to the file name in the bash shell script.


Yeah, that will save a lot of heartburn:)



Thanks,

Rich





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

Re: Table update: restore or replace?

[Ron]

On 5/14/19 3:59 PM, Rich Shepard wrote:

On Tue, 14 May 2019, Adrian Klaver wrote:


A file level backup or database dump?


Adrian,

File level. April 20th is my most recent database dump because I forgot to
run it last Friday afternoon.


Note that referring to file level copies as dumps can be *very* confusing in 
the Postgres world because the pg_dump command does logical backups, and 
they're called dump files.


--
Angular momentum makes the world go 'round.

Re: Re: Table update: restore or replace?

[Rich Shepard]

On Tue, 14 May 2019, Brad Nicholson wrote:


Might I suggest you setup proper backups with continuous archiving
instead? If you had those, you would be able to restore this database back
to the point right before you issued the bad update statement.

I'd highly recommend pgBackRest for the task.


Brad,

I'll certainly take a look at pgBackRest.

Thanks,

Rich

Re: Table update: restore or replace?

[Rich Shepard]

On Tue, 14 May 2019, Adrian Klaver wrote:


Or just fetch them from the table data you have saved in the dump file.


Adrian,

Sigh. I should have thought of that. A great time saver.

Thanks,

Rich

Re: Table update: restore or replace? [RESOLVED]

[Rich Shepard]

On Tue, 14 May 2019, Adrian Klaver wrote:


Or just fetch them from the table data you have saved in the dump file.


Adrian,

This did save a lot of time! Thanks for the pointer.


And I think I'll set up a cron job to do a database dump each day with the
date appended to the file name in the bash shell script.



Yeah, that will save a lot of heartburn:)


Done. Cron will run the shell script at 02:15 each day. This will do until I
build, install, and configure pgBackRest (which consumes less disk space
than do the daily pg_dumpall files.

Best regards,

Rich

Re: Table update: restore or replace?

[Stephen Frost]

Greetings,

* Rich Shepard (rshep...@appl-ecosys.com) wrote:
> That's why I thought of copying the entire data/ directory.

That isn't going to work because things change in the data directory...

> >Also, I don't know what method you've been using to make file-level
> >backups, but they're really pretty worthless unless you (a) stop the
> >server or (b) use a filesystem snapshot. Otherwise you're very likely to
> >have inconsistent data.
> 
> I run dirvish  which runs each night starting at
> 00:30 am when there's no activity (by me, at least) on the database.

The database system is potentially doing things though, so this isn't a
backup solution that is reliable.  You really should be using a backup
solution that's been specifically written to work with PostgreSQL.

I wouldn't trust performing a restore from a backup taken like this.
I'd suggest you restore to a new server (or another directory, at
least...) and try starting up PG and then dump out the table and then
check that it's valid.

And then switch to a backup system that actually works with PG.

Thanks,

Stephen


signature.asc
Description: PGP signature