psql "\d" no longer working
Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still works riftehr=> \dt act* List of relations Schema | Name | Type | Owner +---+---+--- cell | actual_and_inf_rel_clean_final | table | cell cell | actual_and_inf_rel_clean_final_count_rels | table | cell cell | actual_and_inf_rel_part1 | table | cell cell | actual_and_inf_rel_part1_unique | table | cell cell | actual_and_inf_rel_part1_unique_clean | table | cell cell | actual_and_inf_rel_part2 | table | cell cell | actual_and_inf_rel_part2_unique | table | cell cell | actual_and_inf_rel_part2_unique_clean | table | cell (8 rows) riftehr=> select version(); version - PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit (1 row) The server was restarted Friday morning (according to systemctl) and the log file has the complete sql statement: 2023-02-10 13:42:55.214 MST [524159] STATEMENT: SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.oid = '219319'; 2023-02-10 13:43:01.143 MST [524159] ERROR: column c.relhasoids does not exist at character 80 but I don't see any other issue in the log file. I have yet to find another broken meta-command and no sql of mine has failed along similar lines as had "\d" Any pointers much appreciated.
Re: psql "\d" no longer working
Hello Rob, Am 12.02.2023 um 10:02 schrieb Rob Sargent: Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still works riftehr=> \dt act* List of relations Schema | Name | Type | Owner +---+---+--- cell | actual_and_inf_rel_clean_final | table | cell cell | actual_and_inf_rel_clean_final_count_rels | table | cell cell | actual_and_inf_rel_part1 | table | cell cell | actual_and_inf_rel_part1_unique | table | cell cell | actual_and_inf_rel_part1_unique_clean | table | cell cell | actual_and_inf_rel_part2 | table | cell cell | actual_and_inf_rel_part2_unique | table | cell cell | actual_and_inf_rel_part2_unique_clean | table | cell (8 rows) riftehr=> select version(); version - PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit (1 row) The server was restarted Friday morning (according to systemctl) and the log file has the complete sql statement: 2023-02-10 13:42:55.214 MST [524159] STATEMENT: SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.oid = '219319'; 2023-02-10 13:43:01.143 MST [524159] ERROR: column c.relhasoids does not exist at character 80 but I don't see any other issue in the log file. I have yet to find another broken meta-command and no sql of mine has failed along similar lines as had "\d" Any pointers much appreciated. Check the version of your psql binary. I assume it's below v13. There was a change in pg_catalog. Clients below 13 assume, the column is still there. kind regards Georg
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
On Sun, Feb 12, 2023 at 7:22 AM wrote: > > 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. > Given that you have "trust" in your pg_hba.conf this point seems immaterial. > BTW, I thought postgres user has permission to read/write everything in all > databases. Am I missing something obvious? > > Run \du+ and see what all roles are presently installed in the system and what their permissions are. I strongly suspect your server has been hacked and you'll find roles there that don't belong, and the postgres role stripped of its superuser status. David J.
Re: psql "\d" no longer working
On 2/12/23 01:24, Georg H. wrote: Hello Rob, Check the version of your psql binary. I assume it's below v13. There was a change in pg_catalog. Clients below 13 assume, the column is still there. That would be 11 and below. kind regards Georg -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql "\d" no longer working
On 2/12/23 03:02, Rob Sargent wrote: Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still works riftehr=> \dt act* List of relations Schema | Name | Type | Owner +---+---+--- cell | actual_and_inf_rel_clean_final | table | cell cell | actual_and_inf_rel_clean_final_count_rels | table | cell cell | actual_and_inf_rel_part1 | table | cell cell | actual_and_inf_rel_part1_unique | table | cell cell | actual_and_inf_rel_part1_unique_clean | table | cell cell | actual_and_inf_rel_part2 | table | cell cell | actual_and_inf_rel_part2_unique | table | cell cell | actual_and_inf_rel_part2_unique_clean | table | cell (8 rows) riftehr=> select version(); version - PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit (1 row) The server was restarted Friday morning (according to systemctl) and the log file has the complete sql statement: 2023-02-10 13:42:55.214 MST [524159] STATEMENT: SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.oid = '219319'; 2023-02-10 13:43:01.143 MST [524159] ERROR: column c.relhasoids does not exist at character 80 but I don't see any other issue in the log file. I have yet to find another broken meta-command and no sql of mine has failed along similar lines as had "\d" Any pointers much appreciated. What is your search_path set to? -- Born in Arizona, moved to Babylonia.
Re: psql "\d" no longer working
On 2/12/23 08:49, Ron wrote: On 2/12/23 03:02, Rob Sargent wrote: 2023-02-10 13:42:55.214 MST [524159] STATEMENT: SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.oid = '219319'; 2023-02-10 13:43:01.143 MST [524159] ERROR: column c.relhasoids does not exist at character 80 but I don't see any other issue in the log file. I have yet to find another broken meta-command and no sql of mine has failed along similar lines as had "\d" Any pointers much appreciated. What is your search_path set to? I doubt that is the problem as the issue is the column in the table not finding the table. pg_class.relhasoids no longer exists 12+, so the post from Georg is probably pointing in the right direction. -- Born in Arizona, moved to Babylonia. -- Adrian Klaver adrian.kla...@aklaver.com
Re: psql "\d" no longer working
I doubt that is the problem as the issue is the column in the table not finding the table. pg_class.relhasoids no longer exists 12+, so the post from Georg is probably pointing in the right direction. Ah, yes. My client machine at compute centre has to be told to put version 14 on the path. My session there got axed/restarted and I left that part out. Thanks a ton. rjs
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
Re: pg_dump problem with postgres user
On 2/12/23 13:10, ertan.kucuko...@1nar.com.tr wrote: 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. Who is they? Is it possible to put everything back to normal? If it was some outside entity that you don't know that changed the permissions then you can't trust the instance any more. Can I use single user mode to change passwords and permissions? Thanks & Regards, Ertan -- Adrian Klaver adrian.kla...@aklaver.com
[Testcase] Queries running forever, because of wrong rowcount estimate
CREATE DATABASE ttc
WITH
OWNER = admin
ENCODING = 'UTF8'
LC_COLLATE = 'de_DE.UTF-8'
LC_CTYPE = 'de_DE.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
select version();
PostgreSQL 12.13 on amd64-portbld-freebsd13.1, compiled by FreeBSD clang
version 13.0.0 (g...@github.com:llvm/llvm-project.git
llvmorg-13.0.0-0-gd7b669b3a303), 64-bit
CREATE TABLE public.incoming
(
id bigint NOT NULL,
data text COLLATE pg_catalog."default",
CONSTRAINT incoming_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;
insert into incoming(id) select generate_series(1,100);
update incoming set data = 'EINS' where data IS NULL;
insert into incoming(id) select generate_series(101,200);
update incoming set data = 'ZWEI' where data IS NULL;
insert into incoming(id) select generate_series(201,300);
update incoming set data = 'DREI' where data IS NULL;
CREATE TABLE IF NOT EXISTS public.data
(
data text COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT data_pkey PRIMARY KEY (data)
)
TABLESPACE pg_default;
insert into data (data) values ('EINS'), ('DREI');
analyze public.data;
analyze public.incoming;
explain
select distinct data
from incoming
Left outer join public.data using (data)
where data.data is null;
HashAggregate (cost=67371.04..67371.07 rows=3 width=5)
Group Key: incoming.data
-> Hash Anti Join (cost=0.55..64871.04 rows=100 width=5)
Hash Cond: (incoming.data = data.data)
-> Seq Scan on incoming (cost=0.00..44745.50 rows=300 width=5)
-> Hash (cost=0.52..0.52 rows=2 width=5)
-> Seq Scan on data (cost=0.00..0.52 rows=2 width=5)
delete from data;
vacuum analyze data;
explain
select distinct data
from incoming
Left outer join public.data using (data)
where data.data is null;
Unique (cost=56056.08..56056.09 rows=1 width=5)
-> Sort (cost=56056.08..56056.09 rows=1 width=5)
Sort Key: incoming.data
-> Hash Anti Join (cost=60.58..56056.07 rows=1 width=5)
Hash Cond: (incoming.data = data.data)
-> Seq Scan on incoming (cost=0.00..44745.50 rows=300
width=5)
-> Hash (cost=29.70..29.70 rows=2470 width=5)
-> Seq Scan on data (cost=0.00..29.70 rows=2470 width=5)
"rows=1" in the "Hash Anti Join" line is WRONG. It should be
300. Or at least some thousands.
On the next-higher level there will now a Nested Loop chosen. And that
Nested Loop will do whatever costly things it needs to do - only not 1
time but three million times.
I think I have a workaround patch also.
---
Server Configuration Tuning:
< #port = 5432 # (change requires restart)
< #max_connections = 100# (change requires restart)
---
> port = 5434 # (change requires restart)
> max_connections = 60 # (change requires restart)
< #shared_buffers = 32MB# min 128kB
---
> shared_buffers = 40MB # min 128kB
< #temp_buffers = 8MB # min 800kB
---
> temp_buffers = 20MB # min 800kB
< #work_mem = 4MB # min 64kB
< #maintenance_work_mem = 64MB # min 1MB
---
> work_mem = 50MB # min 64kB
> maintenance_work_mem = 50MB # min 1MB
< #max_stack_depth = 2MB# min 100kB
---
> max_stack_depth = 40MB# min 100kB
< #max_files_per_process = 1000 # min 25
---
> max_files_per_process = 200 # min 25
< #effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
---
> effective_io_concurrency = 5 # 1-1000; 0 disables prefetching
< #max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
---
> max_parallel_workers_per_gather = 0 # taken from max_parallel_workers
< #synchronous_commit = on # synchronization level;
---
> synchronous_commit = off # synchronization level;
< #wal_sync_method = fsync # the default is the first option
---
> wal_sync_method = fsync # the default is the first option
< #full_page_writes = on# recover from partial page
writes
---
> full_page_writes = off# recover from partial page
> writes
< #wal_init_zero = on # zero-fill new WAL files
---
> wal_init_zero = off # zero-fill new WAL files
< #wal_writer_delay = 200ms # 1-1 milliseconds
---
> wal_writer_delay = 2000ms # 1-1 milliseconds
< #checkpoint_timeout = 5min# range 30s-1d
---
> checkpoint_timeout = 10min# range 30s-1d
< #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
---
> checkpoint_completion_target = 0.0# checkpoint target du
