Errors after cloning OS to new disk under Hyper-V
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: Errors after cloning OS to new disk under Hyper-V
-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.
Cannot restore windows dump on linux
Hello, I am using PostgreSQL 14.5 I tried to move a Linux database to Windows. Both identical version. Linux dump successfully loaded on Windows system. Reason for changing system didn't work out and now I am trying to move it back because it has some modifications. I just dumped a database backup on windows to a dump file. I see dump file contains "\r\n" as new line identifier (hence windows dump). Just to clarify, this is not \N character I am mixing. When I try to restore windows dump on regular Linux system, I get a lot of errors and it stops at this one below where this is a pure data load position. ERROR: syntax error at or near "43589" LINE 1: 43589 7102e523-f401-4cce-852d-e537f863886f. I also tried to stop at first error when restoring, in this case it stops at below error root@app:/home/ek# psql -v ON_ERROR_STOP=1 -U myuser -d mydb < last_backup.bak SET SET SET SET SET set_config (1 satır) SET SET SET SET ERROR: index "ix_xrates_date" does not exist I searched the internet and I could not solve my problem. How can I successfully dump on Widnows and restore on Linux? Any help is appreciated. Thanks & Regards, Ertan
RE: Cannot restore windows dump on linux
Hello again, While I was searching for a solution, I saw an example of manual running of dump file within psql. Tried this and it did work just fine. \connect mydb \i last_backup.bak Above two commands completed without any loading error at all. Now I wonder why command line did not work. Do let me know if there is a known reason, please. Thanks & Regards, Ertan - From: ertan.kucuko...@1nar.com.tr Sent: Wednesday, October 19, 2022 18:43 To: pgsql-general@lists.postgresql.org Subject: Cannot restore windows dump on linux Hello, I am using PostgreSQL 14.5 I tried to move a Linux database to Windows. Both identical version. Linux dump successfully loaded on Windows system. Reason for changing system didn't work out and now I am trying to move it back because it has some modifications. I just dumped a database backup on windows to a dump file. I see dump file contains "\r\n" as new line identifier (hence windows dump). Just to clarify, this is not \N character I am mixing. When I try to restore windows dump on regular Linux system, I get a lot of errors and it stops at this one below where this is a pure data load position. ERROR: syntax error at or near "43589" LINE 1: 43589 7102e523-f401-4cce-852d-e537f863886f. I also tried to stop at first error when restoring, in this case it stops at below error root@app:/home/ek# psql -v ON_ERROR_STOP=1 -U myuser -d mydb < last_backup.bak SET SET SET SET SET set_config (1 satır) SET SET SET SET ERROR: index "ix_xrates_date" does not exist I searched the internet and I could not solve my problem. How can I successfully dump on Widnows and restore on Linux? Any help is appreciated. Thanks & Regards, Ertan
High CPU usage
Hello,
I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very
high CPU usage on my Linux system as below
PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+
COMMAND
2357756 postgres 20 0 2441032 2,3g 4 S 298,7 67,9 2114:58
Tspjzj2Z
I could not find any file named Tspjzj2Z on the file system. I could not
find PID number using below SQL
SELECT * FROM pg_stat_activity
I also see another suggestion like below to identify long running queries
SELECT max(now() - xact_start) FROM pg_stat_activity
WHERE state IN ('idle in transaction',
'active');
I get no long running query at all.
There is no replication of any kind. This is a single instance server which
alows certification login only.
I appreciate any help to figure this out.
Thanks & Regards,
Ertan
pg_dump problem with postgres user
Hello, I am using postgresql 14.6. PostgreSQL 15.1 is also installed in my system. I just recognize that my scheduled backups are failing. I am using postgres user for backup user and .pgpass file for no password prompt. Result is same even I force a password prompt. Google results are I get are mostly for directory permission problems people are having. What I tried is as following. ek@app:~$ pg_dump -U postgres -h localhost -p 5432 -f test.bak counter pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table usbserial pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE ek@app:~$ pg_dump -U postgres -h localhost -p 5432 -W -f test.bak counter Parola: pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table usbserial pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE ek@app:~$ su - Parola: root@app:~# pg_dump -U postgres -h localhost -p 5432 -f test.bak counter pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table usbserial pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE root@app:~# su - postgres postgres@app:~$ pg_dump -U postgres -h localhost -p 5432 -f test.bak counter pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table usbserial pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE postgres@app:~$ psql counter psql (15.1 (Debian 15.1-1.pgdg100+1), server 14.6 (Debian 14.6-1.pgdg100+1)) Type "help" for help. counter=> \l+ List of databases Name | Owner| Encoding | Collate |Ctype| ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description ---++--+-+-+ +-+---+-++-- -- counter | counter| UTF8 | tr_TR.UTF-8 | tr_TR.UTF-8 | | libc| | 8593 kB | pg_default | (other databases are cropped from list) counter=> \dt+ List of relations Schema | Name| Type | Owner | Persistence | Access method | Size | Description +---+---+-+-+---+--- +- public | usbserial | table | counter | permanent | heap | 16 kB | (1 row) counter=> \q postgres@app:~$ Relevant lines from pg_hba.conf file is as following # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32trust # IPv6 local connections: hostall all ::1/128 md5 I have following line in my .pgpass file ek@app:~$ cat .pgpass localhost:5432:*:postgres: This was all working. I cannot remember what I changed, when I changed. Old backups are automatically deleted after certain days and that prevents me pointing to exact date of change. BTW, I thought postgres user has permission to read/write everything in all databases. Am I missing something obvious? Any help is appreciated. Thanks & Regards, Ertan
Re: pg_dump problem with postgres user
Hello,
My service provider spam filter just blocked your message and so I send a
separate one.
Your suspicion seems like correct. Postgres user is no more a superuser.
Seems like they added postgresql_user and made it superuser.
I thought I only allow access with certificate to the database.
OS access is limited to single IP. Probably this is the weak point.
I should read more about security in general.
postgres=> \du+
List of roles
Role name| Attributes| Member
of | Description
-+-+
---+-
postgres| Create role, Create DB, Replication, Bypass RLS | {}
|
postgresql_user | Superuser | {}
|
postgres=>
Is it possible to put everything back to normal?
Can I use single user mode to change passwords and permissions?
Thanks & Regards,
Ertan
Suppress logging of "pg_hba.conf rejects connection for host"
Hello, One of the systems running PostgreSQL 14.7 receive a lot of lines like in the subject. I have below pg_hba.conf line and that line causes these to be logged. host all all 0.0.0.0/0 reject If possible, I do not want to see these lines in my logs. But, I failed to find a parameter for it. Is it possible to turn this specific message logging off? Thanks & Regards, Ertan
