Re: Errors after cloning OS to new disk under Hyper-V

[Tom Lane]

 writes:
> From: Rob Sargent  
>> Have you tried to REINDEX as suggested?

> I cannot get access to psql. If there is a way to reindex without having to 
> login first, I do not know how to do that.

The disaster recovery process that Rob is suggesting goes like this:

* Shut down server, if it's running

* Start a standalone backend with the -P (disable system indexes) switch:

postgres --single -P database_name

* REINDEX everything in sight, or at least REINDEX SYSTEM

* Quit standalone backend (control-D on Unix, less sure about Windows)

* Repeat for each database you care about

* Restart server

TBH, though, I have no expectation that this will help you.  It can
work for fixing a situation with limited damage to system catalog
indexes; but it cannot fix damage to the catalog tables themselves.
Given that the damage seems to have been done by a faulty filesystem-level
copying tool, there's little reason to hope that only the indexes and
not the tables were damaged.  No non-Postgres-specific tool would be
likely to know the difference, because they're all just files.

But, having said that, the error messages you quoted seem to be
complaining only about indexes.  Maybe it's worth trying this.

Failing that, you could call in a professional data recovery
consultant, if the data is valuable enough for that to make sense.
But this sort of situation is not likely to be recoverable
without detailed expert help, and maybe not then.  (A word of
advice: if calling in outside help is even faintly possible,
don't do *anything* without first making a filesystem-level
backup that you can trust.  Otherwise your own attempts might
just break things beyond the last chance of repair.)

regards, tom lane

Re: Errors after cloning OS to new disk under Hyper-V

[Pavel Stehule]

so 18. 7. 2020 v 6:36 odesílatel  napsal:

> -Original Message-
> From: Rob Sargent 
> Sent: Saturday, July 18, 2020 5:56 AM
> To: pgsql-general@lists.postgresql.org
> Subject: Re: Errors after cloning OS to new disk under Hyper-V
>
> > Have you tried to REINDEX as suggested?
>
> I cannot get access to psql. If there is a way to reindex without having
> to login first, I do not know how to do that.
>

you should to run postgres in single-user mode

https://www.postgresql.org/docs/current/app-postgres.html




*postgres --single -D /usr/local/pgsql/data other-options my_database*

RE: Errors after cloning OS to new disk under Hyper-V

[ertan.kucukoglu]

-Original Message-
From: Rob Sargent  
Sent: Saturday, July 18, 2020 5:56 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Errors after cloning OS to new disk under Hyper-V

> Have you tried to REINDEX as suggested?

I cannot get access to psql. If there is a way to reindex without having to 
login first, I do not know how to do that.

Re: Errors after cloning OS to new disk under Hyper-V

[Rob Sargent]

On 7/17/20 8:51 PM, ertan.kucuko...@1nar.com.tr wrote:

Hello,

This is PostgreSQL v10.10 64Bit, running on Windows 10 64Bit.

One user, by himself, without asking for help, cloned his OS from one disk to 
Hyper-V disk. Old disk was not accessed, OS was shutdown while he is doing the 
cloning. He used some kind of tool that I do not know for that purpose. New 
server is running under Hyper-V.

Once he saw Hyper-V server is up and running he deleted old disk for good. 
Unfortunately, new system has more than brief problems. He has no backup of the 
database cluster or particular database(s) in it.

I see in log following errors:
2020-07-16 12:28:17.598 +03 [3964] HATA (ERROR):  "pg_opclass_oid_index" 
indeksinde 0 bloğunda beklenmeyen boş sayfa
2020-07-16 12:28:17.598 +03 [3964] İPUCU:Lütfen onu REINDEX'leyin.
2020-07-16 12:29:02.065 +03 [2744] LOG:  istemciden veri alınamamıştır: 
unrecognized winsock error 10054
2020-07-16 12:29:17.699 +03 [3748] HATA (ERROR):  "pg_opclass_oid_index" 
indeksinde 0 bloğunda beklenmeyen boş sayfa
2020-07-16 12:29:17.699 +03 [3748] İPUCU:Lütfen onu REINDEX'leyin.
2020-07-16 12:30:17.805 +03 [1456] HATA (ERROR):  "pg_opclass_oid_index" 
indeksinde 0 bloğunda beklenmeyen boş sayfa
2020-07-16 12:30:17.805 +03 [1456] İPUCU:Lütfen onu REINDEX'leyin.
2020-07-16 12:30:47.363 +03 [7460] ÖLÜMCÜL (FATAL):  base/16394/2684 nesnesinin 
0 bloğunda geçersiz sayfa
2020-07-16 12:31:04.009 +03 [1240] ÖLÜMCÜL (FATAL):  base/16394/2684 nesnesinin 
0 bloğunda geçersiz sayfa
2020-07-16 12:31:11.261 +03 [2396] ÖLÜMCÜL (FATAL):  base/16394/2684 nesnesinin 
0 bloğunda geçersiz sayfa

Basic translation to English is:
ERROR: "pg_opclass_oid_index" index 0 block unexpected empty page
HINT:Please REINDEX it.
LOG:  cannot read fata from client: unrecognized winsock error 10054
FATAL:  base/16394/2684 object 0 block invalid page

When I try to connect server on command line using psql I get same error:
PS C:\Program Files\PostgreSQL\10 - Kopya\bin> .\psql.exe -U postgres
psql: ÖLÜMCÜL (FATAL):  "pg_opclass_oid_index" indeksinde 0 bloğunda 
beklenmeyen boş sayfa
İPUCU:  Lütfen onu REINDEX'leyin.

PS C:\Program Files\PostgreSQL\10 - Kopya\bin> .\psql.exe -U postgres -h 
localhost
psql: ÖLÜMCÜL (FATAL):  "pg_opclass_oid_index" indeksinde 0 bloğunda 
beklenmeyen boş sayfa
İPUCU:  Lütfen onu REINDEX'leyin.

I am told that some databases in cluster can be accessed, not all. They are 
accessed from an application and not by admin tools. I do not know how many 
databases exists in the cluster or their names.

I have tried to dump everything and that also failed:
PS C:\logs> & 'C:\Program Files\PostgreSQL\10 - Kopya\bin\pg_dumpall.exe' -f 
dump.bak -U postgres
pg_dump: [arşivleyici (db)] sorgu başarısız oldu: HATA (ERROR):  
base/16394/2684 nesnesinin 0 bloğunda geçersiz sayfa
pg_dump: [arşivleyici (db)] sorgu şu idi: SELECT 
pg_catalog.set_config('search_path', '', false)
pg_dumpall: pg_dump "ZEntegre" veritabanında başarısız oldu, çıkılıyor

I wonder if there is anything that can be done here.

Thanks & Regards,
Ertan




Have you tried to REINDEX as suggested?

Errors after cloning OS to new disk under Hyper-V

[ertan.kucukoglu]

Hello,

This is PostgreSQL v10.10 64Bit, running on Windows 10 64Bit.

One user, by himself, without asking for help, cloned his OS from one disk to 
Hyper-V disk. Old disk was not accessed, OS was shutdown while he is doing the 
cloning. He used some kind of tool that I do not know for that purpose. New 
server is running under Hyper-V.

Once he saw Hyper-V server is up and running he deleted old disk for good. 
Unfortunately, new system has more than brief problems. He has no backup of the 
database cluster or particular database(s) in it.

I see in log following errors:
2020-07-16 12:28:17.598 +03 [3964] HATA (ERROR):  "pg_opclass_oid_index" 
indeksinde 0 bloğunda beklenmeyen boş sayfa
2020-07-16 12:28:17.598 +03 [3964] İPUCU:Lütfen onu REINDEX'leyin.
2020-07-16 12:29:02.065 +03 [2744] LOG:  istemciden veri alınamamıştır: 
unrecognized winsock error 10054
2020-07-16 12:29:17.699 +03 [3748] HATA (ERROR):  "pg_opclass_oid_index" 
indeksinde 0 bloğunda beklenmeyen boş sayfa
2020-07-16 12:29:17.699 +03 [3748] İPUCU:Lütfen onu REINDEX'leyin.
2020-07-16 12:30:17.805 +03 [1456] HATA (ERROR):  "pg_opclass_oid_index" 
indeksinde 0 bloğunda beklenmeyen boş sayfa
2020-07-16 12:30:17.805 +03 [1456] İPUCU:Lütfen onu REINDEX'leyin.
2020-07-16 12:30:47.363 +03 [7460] ÖLÜMCÜL (FATAL):  base/16394/2684 nesnesinin 
0 bloğunda geçersiz sayfa 
2020-07-16 12:31:04.009 +03 [1240] ÖLÜMCÜL (FATAL):  base/16394/2684 nesnesinin 
0 bloğunda geçersiz sayfa 
2020-07-16 12:31:11.261 +03 [2396] ÖLÜMCÜL (FATAL):  base/16394/2684 nesnesinin 
0 bloğunda geçersiz sayfa

Basic translation to English is:
ERROR: "pg_opclass_oid_index" index 0 block unexpected empty page
HINT:Please REINDEX it.
LOG:  cannot read fata from client: unrecognized winsock error 10054
FATAL:  base/16394/2684 object 0 block invalid page

When I try to connect server on command line using psql I get same error:
PS C:\Program Files\PostgreSQL\10 - Kopya\bin> .\psql.exe -U postgres
psql: ÖLÜMCÜL (FATAL):  "pg_opclass_oid_index" indeksinde 0 bloğunda 
beklenmeyen boş sayfa
İPUCU:  Lütfen onu REINDEX'leyin.

PS C:\Program Files\PostgreSQL\10 - Kopya\bin> .\psql.exe -U postgres -h 
localhost
psql: ÖLÜMCÜL (FATAL):  "pg_opclass_oid_index" indeksinde 0 bloğunda 
beklenmeyen boş sayfa
İPUCU:  Lütfen onu REINDEX'leyin.

I am told that some databases in cluster can be accessed, not all. They are 
accessed from an application and not by admin tools. I do not know how many 
databases exists in the cluster or their names.

I have tried to dump everything and that also failed:
PS C:\logs> & 'C:\Program Files\PostgreSQL\10 - Kopya\bin\pg_dumpall.exe' -f 
dump.bak -U postgres
pg_dump: [arşivleyici (db)] sorgu başarısız oldu: HATA (ERROR):  
base/16394/2684 nesnesinin 0 bloğunda geçersiz sayfa
pg_dump: [arşivleyici (db)] sorgu şu idi: SELECT 
pg_catalog.set_config('search_path', '', false)
pg_dumpall: pg_dump "ZEntegre" veritabanında başarısız oldu, çıkılıyor

I wonder if there is anything that can be done here.

Thanks & Regards,
Ertan

Re: About compress in pg_dump

[Adrian Klaver]

On 7/17/20 8:12 AM, Diego wrote:
Yep, I transfer backups files all the time with -Fc and never the 
problem was rsync


Using --rsyncable with gzip really helps if you rsync the compressed 
file to somewhere else it exists. It greatly reduces the amount of data 
sent for files that a do not have a massive amount of changes between syncs.




On 2020-07-17 12:07, David G. Johnston wrote:
On Fri, Jul 17, 2020 at 7:49 AM Edmundo Robles > wrote:


To backup  a database  I do:
 nice -n +19  pg_dump -Fc  database | nice -n +19 gzip --rsyncable
  -nc >  database.dump

If -Fc  option  is compressed  by default  I dont need gzip the
backup,  but I need pass --rsyncable and -n options.

How can  I pass  gzip options  to compress in pg_dump?


pg_dump isn't using the gzip program, it's just performing compression 
per the gzip compression specification, and doesn't provide those two 
features to control it's processing (or any features beyond what's 
documented on the pg_dump reference page).


David J.




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

Re: About compress in pg_dump

[Adrian Klaver]

On 7/17/20 7:48 AM, Edmundo Robles wrote:

To backup  a database  I do:
  nice -n +19  pg_dump -Fc  database | nice -n +19 gzip --rsyncable   
-nc >  database.dump


If -Fc  option  is compressed  by default  I dont need gzip the backup,  
but I need pass --rsyncable  and -n options.


How can  I pass  gzip options  to compress in pg_dump?

if not   I will use :
nice -n +19  pg_dump -Fc -Z 0 database | nice -n +19 gzip --rsyncable   
-nc >  database.dump

but I dont want to do that. :)


Do you need the custom format on the other end?

Or can you just use the plain format dump and pipe that to gzip with 
appropriate options?


You will then need to use psql to do the restore though.



Thanks   for your help...

--




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

Re: python async with psycopg2

[Daniele Varrazzo]

On Fri, 17 Jul 2020 at 20:44, Rita  wrote:>

> curs = conn.cursor()
> curs.execute("LISTEN mychan0;")
> #curs.execute("LISTEN mychan1;") #fails!
> wait_select(conn)

Maybe you have to wait_select after each execute.

This example could be helpful.
https://www.psycopg.org/docs/advanced.html#asynchronous-notifications
Note that you don't need an async connection to receive notification:
a sync one, as long as is in autocommit and you are using an I/O
completion function to wait for readiness, works as well.

-- Daniele

python async with psycopg2

[Rita]

I am trying to listen to multiple channels thru pg_notify mechanism.

select pg_notify('mychan0',foo');
select pg_notify('mychan'1,'bar');

In python I have something like this

import select,time
import psycopg2
import psycopg2.extensions
from psycopg2.extras import wait_select

DSN="dbname=mydb user=user host=localhost"

def main():

conn = psycopg2.connect(DSN,async_=True)
wait_select(conn)
curs = conn.cursor()
curs.execute("LISTEN mychan0;")
#curs.execute("LISTEN mychan1;") #fails!
wait_select(conn)
while True:
wait_select(conn)
while conn.notifies:
print("Notify: %s"%conn.notifies.pop().payload)
time.sleep(1)
conn.close()

I keep getting

psycopg2.ProgrammingError: execute cannot be used while an asynchronous
query is underway

I prefer to stick with psycopg2 library instead of a wrapper.
What am I doing wrong?

-- 
--- Get your facts first, then you can distort them as you please.--

DB Authentication with Label Security

[Ailleen Pace]

Oracle has a product called Oracle Label Security using Oracle Internet 
Directory.  Does PostgreSQL have a similar capability?

Thank you in advance!

Re: PostgreSQL make too long to start.

[David G. Johnston]

On Fri, Jul 17, 2020 at 9:16 AM FOUTE K. Jaurès 
wrote:

> It is make sense that PostgreSQL make too long to start, About 20
> minutes.  I'm using PostgreSQL 12 intalling on Ubuntu Server 18.04 and my
> database is about 25 GO  of data.
>

Every time?  How are you shutting down the server?

Additionally, you will want to examine, and probably relay, the contents of
the log file during these shutdown/startup periods.

David J.

PostgreSQL make too long to start.

[FOUTE K . Jaurès]

It is make sense that PostgreSQL make too long to start, About 20 minutes.
I'm using PostgreSQL 12 intalling on Ubuntu Server 18.04 and my database is
about 25 GO  of data.

Re: Security Vulnerability on PostgreSQL VMs

[Magnus Hagander]

On Fri, Jul 17, 2020 at 5:44 PM Hilbert, Karin  wrote:

> We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various Linux
> VMs with Red Hat Enterprise Linux Server release 7.8 (Maipo) OS.  We're
> also running repmgr v5.1.0 & PgBouncer v1.13.
>
> We're getting vulnerability reports from our Security Office for the
> following packages:
>  - python-pulp-agent-lib-2.13.4.16-1.el7sat
>  - python-gofer-2.12.5-5.el7sat
>
> For some reason these packages aren't being updated to the current
> versions & our Linux Admins haven't been able to resolve the update
> issue.  It has something to do with a satellite?   (I'm not a Linux Admin -
> I don't really know what they're talking about).  Anyway, *are these
> packages anything that would be required by PostgreSQL, repmgr or
> PgBouncer?*  It's nothing that I installed on the VMs - I assume that
> it's something installed along with the OS.  The Linux Admin's
> recommendation is to just remove these packages.
>

They are not. They are part Pulp for example, but in particular they are
part of RedHat Satellite which is probably why the package version has a
name ending in "sat". So it would be something a Linux admin would put in
there, not the DBA.

But to answer the question, no they are not required by PostgreSQL, repmgr
or pgbouncer.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Security Vulnerability on PostgreSQL VMs

[Diego]

Hi!


Try with "yum deplist " to check who app use phyton.


Diego,


On 2020-07-17 12:44, Hilbert, Karin wrote:
We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various 
Linux VMs with Red Hat Enterprise Linux Server release 7.8 (Maipo) 
OS.  We're also running repmgr v5.1.0 & PgBouncer v1.13.


We're getting vulnerability reports from our Security Office for the 
following packages:

 - python-pulp-agent-lib-2.13.4.16-1.el7sat
 - python-gofer-2.12.5-5.el7sat

For some reason these packages aren't being updated to the current 
versions & our LinuxAdmins haven't been able to resolve the update 
issue.  It has something to do with a satellite?  (I'm not a Linux 
Admin - I don't really know what they're talking about).  Anyway, *are 
these packages anything that would be required by PostgreSQL, repmgr 
or PgBouncer?*  It's nothing that I installed on the VMs - I assume 
that it's something installed along with the OS.  The Linux Admin's 
recommendation is to just remove these packages.


Thanks,
Karin Hilbert

Re: Security Vulnerability on PostgreSQL VMs

[Ron]

There has to be some "yum" or "rpm" option to show what depends on those 
packages.


On 7/17/20 10:44 AM, Hilbert, Karin wrote:
We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various Linux 
VMs with Red Hat Enterprise Linux Server release 7.8 (Maipo) OS.  We're 
also running repmgr v5.1.0 & PgBouncer v1.13.


We're getting vulnerability reports from our Security Office for the 
following packages:

 - python-pulp-agent-lib-2.13.4.16-1.el7sat
 - python-gofer-2.12.5-5.el7sat

For some reason these packages aren't being updated to the current 
versions & our LinuxAdmins haven't been able to resolve the update issue.  
It has something to do with a satellite?  (I'm not a Linux Admin - I don't 
really know what they're talking about).  Anyway, *are these packages 
anything that would be required by PostgreSQL, repmgr or PgBouncer?*  It's 
nothing that I installed on the VMs - I assume that it's something 
installed along with the OS.  The Linux Admin's recommendation is to just 
remove these packages.


Thanks,
Karin Hilbert



--
Angular momentum makes the world go 'round.

Security Vulnerability on PostgreSQL VMs

[Hilbert, Karin]

We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various Linux VMs 
with Red Hat Enterprise Linux Server release 7.8 (Maipo) OS.  We're also 
running repmgr v5.1.0 & PgBouncer v1.13.

We're getting vulnerability reports from our Security Office for the following 
packages:
 - python-pulp-agent-lib-2.13.4.16-1.el7sat
 - python-gofer-2.12.5-5.el7sat

For some reason these packages aren't being updated to the current versions & 
our Linux Admins haven't been able to resolve the update issue.  It has 
something to do with a satellite?   (I'm not a Linux Admin - I don't really 
know what they're talking about).  Anyway, are these packages anything that 
would be required by PostgreSQL, repmgr or PgBouncer?  It's nothing that I 
installed on the VMs - I assume that it's something installed along with the 
OS.  The Linux Admin's recommendation is to just remove these packages.

Thanks,

Karin Hilbert

Re: About compress in pg_dump

[Diego]

Yep, I transfer backups files all the time with -Fc and never the 
problem was rsync


On 2020-07-17 12:07, David G. Johnston wrote:
On Fri, Jul 17, 2020 at 7:49 AM Edmundo Robles > wrote:


To backup  a database  I do:
 nice -n +19  pg_dump -Fc  database | nice -n +19 gzip --rsyncable
  -nc >  database.dump

If -Fc  option  is compressed  by default  I dont need gzip the
backup,  but I need pass --rsyncable and -n options.

How can  I pass  gzip options  to compress in pg_dump?


pg_dump isn't using the gzip program, it's just performing compression 
per the gzip compression specification, and doesn't provide those two 
features to control it's processing (or any features beyond what's 
documented on the pg_dump reference page).


David J.

Re: About compress in pg_dump

[David G. Johnston]

On Fri, Jul 17, 2020 at 7:49 AM Edmundo Robles  wrote:

> To backup  a database  I do:
>  nice -n +19  pg_dump -Fc  database | nice -n +19 gzip --rsyncable   -nc
> >  database.dump
>
> If -Fc  option  is compressed  by default  I dont need gzip the backup,
> but I need pass --rsyncable  and -n options.
>
> How can  I pass  gzip options  to compress in pg_dump?
>

pg_dump isn't using the gzip program, it's just performing compression per
the gzip compression specification, and doesn't provide those two features
to control it's processing (or any features beyond what's documented on the
pg_dump reference page).

David J.

About compress in pg_dump

[Edmundo Robles]

To backup  a database  I do:
 nice -n +19  pg_dump -Fc  database | nice -n +19 gzip --rsyncable   -nc >
database.dump

If -Fc  option  is compressed  by default  I dont need gzip the backup,
but I need pass --rsyncable  and -n options.

How can  I pass  gzip options  to compress in pg_dump?

if not   I will use :
nice -n +19  pg_dump -Fc -Z 0 database | nice -n +19 gzip --rsyncable   -nc
>  database.dump
but I dont want to do that. :)

Thanks   for your help...

--

Re: Where Do I Find...

[Paul Förster]

Hi Gene,

> On 17. Jul, 2020, at 16:05, Eugene Poole  wrote:
> 
> I need to know if there is any documentation on what has to be changed in my 
> Oracle DDL so that it works in PgSql and this includes data inserts.

not in general, that I know of. It depends on your Oracle DDL. If you are 
migrating from Oracle to PostgreSQL, then you might be interested to take a 
look at ora2pg.

http://ora2pg.darold.net
https://github.com/darold/ora2pg

Cheers,
Paul

Where Do I Find...

[Eugene Poole]

I need to know if there is any documentation on what has to be changed 
in my Oracle DDL so that it works in PgSql and this includes data inserts.


Gene

Re: Capturing just slow queries

[Tiffany Thang]

Thank you for the information. That's a comprehensive list of monitoring
solutions. I'll take a look.

Tiff

On Fri, Jul 17, 2020 at 4:14 AM Wim Bertels  wrote:

> You might have a look at:
> https://www.postgresql.org/docs/current/auto-explain.html
>
> Also there are several monitoring solutions:
> https://wiki.postgresql.org/wiki/Monitoring
>
>
> Tiffany Thang schreef op do 16-07-2020 om 13:41 [-0400]:
> > Hi,
> > log_min_duration_statement captures all statements including DMLs
> > that have exceeded the threshold. Is there a way in PG 12 to capture
> > just select statements excluding all DMLs and DDLs? In my
> > environment, it's acceptable for DMLs and DDLs to cross the threshold
> > and we are more interested in capturing poor performing select
> > statements.
> >
> > Thanks.
> >
> > Tiff
> --
> mvg,
> Wim Bertels
> --
> Lector
> UC Leuven-Limburg
> --
> There is no hunting like the hunting of man, and those who have hunted
> armed men long enough and liked it, never care for anything else
> thereafter.
> -- Ernest Hemingway
>
>
>
>

Re: Capturing just slow queries

[Wim Bertels]

You might have a look at:
https://www.postgresql.org/docs/current/auto-explain.html

Also there are several monitoring solutions:
https://wiki.postgresql.org/wiki/Monitoring


Tiffany Thang schreef op do 16-07-2020 om 13:41 [-0400]:
> Hi,
> log_min_duration_statement captures all statements including DMLs
> that have exceeded the threshold. Is there a way in PG 12 to capture
> just select statements excluding all DMLs and DDLs? In my
> environment, it's acceptable for DMLs and DDLs to cross the threshold
> and we are more interested in capturing poor performing select
> statements. 
> 
> Thanks.
> 
> Tiff 
-- 
mvg,
Wim Bertels
--
Lector
UC Leuven-Limburg
--
There is no hunting like the hunting of man, and those who have hunted
armed men long enough and liked it, never care for anything else thereafter.
-- Ernest Hemingway