How to migrate database from 10.1 to 9.1

2018-05-13 Thread Andrus
K (id > 0) ) WITH ( OIDS=FALSE ); How to fix this so that sequences are also restored ? I tried to restore using both 10.1 and 9.1 pg_restore but autogenerated primary key columns are still lost. Posted also in https://stackoverflow.com/questions/50317935/how-to-ove-database-from-postgres-10-1-to-9-1 Andrus.

How to create StartsWith function for char(n) type with ending space

2018-07-06 Thread Andrus
turns false. How to fix this so that it returns true? Using Postgres starting from 9.1 Andrus. CREATE or replace FUNCTION public.likeescape( str text ) -- https://stackoverflow.com/questions/10153440/how-to-escape-string-while-matching-pattern-in-postgresql RETURNS text

How to transfer databases form one server to other

2020-01-26 Thread Andrus
restore whole cluster. Andrus.

Re: How to transfer databases form one server to other

2020-01-27 Thread Andrus
New server has 11 GB ram . No other applications are running during database transfer. shared_buffer=1GB setting is currently used in postgresql.conf 5. Can this plan improved Andrus.

Re: How to transfer databases form one server to other

2020-01-27 Thread Andrus
ly no dependencies. Andrus.

Re: How to transfer databases form one server to other

2020-01-27 Thread Andrus
x27;m sorry. Andrus.

How to restore to empty database

2020-01-30 Thread Andrus
tabase which does not exist to drop existing database before restore if it exists ? Should I invoke drop database if exists and create database commands before calling pg_restore or can pg_restore do it itself ? Using Postgres 12 on Debian 10 Andrus.

Re: How to restore to empty database

2020-01-30 Thread Andrus
"database24.backup" & or some balance between those ? Is there some postgres or Debian setting which can used during restore time to speed up restore ? I use shared_buffers=1GB , other settings from debian installation. Andrus.

Re: How to restore to empty database

2020-01-30 Thread Andrus
all the databases and the globals. Source cluster is in old Debian 6 Squeeze running Postgres 9.1 Should I create pipe using pg_dumpall and restore everything from old using pipe instead of pg_dump/pg_restore ? Andrus.

How to restore roles without changing postgres password

2020-02-11 Thread Andrus
-f globals.sql postgres but this changes user postgres password also. How to restore roles so that postgres user password is not changed on restore. Script runs on Debian 10 with Postgres 12 Server from where it reads users runs on Debian Squeeze with Postgres 9.1 Andrus

Re: How to restore roles without changing postgres password

2020-02-11 Thread Andrus
ses appear in file. This looks like noice. GRANT documentation https://www.postgresql.org/docs/current/sql-grant.html does not contain GRANTED BY clause. It looks like pg_dumpall generates undocumented clause. Andrus.

Re: How to restore roles without changing postgres password

2020-02-12 Thread Andrus
t as text. Or it retrieves globals from other cluster using dblink and applies changes to new cluster. This can be called instead of pq_dumpall and can edited for custom needs. Editing plpgsql script is easier for postgres users than creating sed script to delete commands from sql file. Andrus.

How to fix 0xC0000005 exception in Postgres 9.0

2020-02-20 Thread Andrus
OS: Windows server 2008 R2 Version 6.1 Build 7601 Andrus. Postgres log: 2020-02-20 15:44:51 EET LOG: server process (PID 3788) was terminated by exception 0xC005 2020-02-20 15:44:51 EET HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. 202

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-20 Thread Andrus
Hi! Upgrade to a version of Postgres that is not 5 years past EOL? Not possible at moment because requires lot of testing not to break existing applications. Planned in future. Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-20 Thread Andrus
Hi! Given this is localhost connection, start looking at firewall or AV. Windows firewall is turned off. It does not affect to local connections in same computer. I turned windows antivirus off but problem persists. Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-21 Thread Andrus
Hi! Realized I should have been clearer. By pre-libpq I meant this: https://odbc.postgresql.org/docs/release.html psqlODBC 09.05.0100 Release Changes: Use libpq for all communication with the server Previously, libpq was only used for authentication. Using it for all communication lets

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-21 Thread Andrus
d without error. Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-21 Thread Andrus
looked into odbc description and havent found how error message details are returned. Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-21 Thread Andrus
UAGE plpgsql AS $$ BEGIN EXECUTE 'DROP TABLE ' || $1; EXCEPTION WHEN UNDEFINED_TABLE THEN RETURN; END; $$; Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-21 Thread Andrus
ON andmetp FOR EACH STATEMENT EXECUTE PROCEDURE setlastchange() Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-21 Thread Andrus
ent has certain configuration. Maybe I will change application not to send semicolon before SELECT statement. Andrus.

How to get error message details from libpq based psqlODBC driver (regression)

2020-02-22 Thread Andrus
2.2 and latest psqlODBC driver 12.01. are used. psqlODBC is called from Visual FoxPro Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

2020-02-22 Thread Andrus
sted error message details issue long time ago in this pgsql-odbc list but havent got solution. I posted it as separate message here and in https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details Andrus.

Re: How to get error message details from libpq based psqlODBC driver (regression)

2020-02-23 Thread Andrus
insert or update on table "rid" violates foreign key constraint "rid_yhik_fkey" Andrus.

Re: How to get error message details from libpq based psqlODBC driver (regression)

2020-02-23 Thread Andrus
-odbc source code. Andrus.

Postgres 12 backup in 32 bit windows client

2020-03-28 Thread Andrus
data access. Maybe there is 32 -bit version of pg_dump.exe. Andrus.

Re: Postgres 12 backup in 32 bit windows client

2020-03-28 Thread Andrus
backup from 5432 port directly to her computer C: drive. If Postgres 12 32bit pg_dump is not available maybe it is possible to create stored procedure which invokes pg_dump in server and returns .backup file as bytea value to client: select run('pg_dump mydatabase') Andrus. -- Adr

could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-28 Thread Andrus
locale "et_EE.UTF-8": codeset is "CPUTF-8" How to fix this ? Debian and Windows computer have same settings: Latest Postgres 12 is used OS and database locales are Estonian Database encoding is UTF-8 Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-28 Thread Andrus
ation: Yes Data Execution Prevention Available: Yes Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-29 Thread Andrus
w template with proper encoding or changing template0 encoding helps? Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-29 Thread Andrus
-- template0 | postgres | UTF8 | Estonian_Estonia.1257 | Estonian_Estonia.1257 | =c/postgres + | | | | | postgres=CTc/postgres (1 row) Andrus.

Hot standby from Debian to Windows

2020-03-29 Thread Andrus
goes down, users can change server address to Windows computer as temporary workaround and continue working. Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-29 Thread Andrus
anually WITH OWNER = mydbmanually_owner ENCODING = 'UTF8' LC_COLLATE = 'Estonian_Estonia.1257' LC_CTYPE = 'Estonian_Estonia.1257' TABLESPACE = pg_default CONNECTION LIMIT = -1; Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-29 Thread Andrus
console codepages any way, they operate using only UTF-8 character set since both databases are in UTF-8 I think console code page warning message is not related to this issue. Andrus.

Re: Hot standby from Debian to Windows

2020-03-29 Thread Andrus
two clusters when users continue to enter data, this is not time critical. Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-29 Thread Andrus
sh_United States.1252' LC_CTYPE = 'English_United States.1252'; I verifed that data was restored using pg_restore without manually changing anything. Andrus.

Re: Hot standby from Debian to Windows

2020-03-29 Thread Andrus
. pgpool should switch to use it for production work only if main server does not respond. I havent found this feature in pgpool documentation (maybe missed). Andrus.

Re: Hot standby from Debian to Windows

2020-03-30 Thread Andrus
+Hyper-V+ binary replication allows to connect more than 20 users and may be best solution. Both have 64-bit OS. Will binary replication work in this case. Andrus.

Re: could not determine encoding for locale "et_EE.UTF-8": codeset is "CPUTF-8" in pg_restore

2020-03-30 Thread Andrus
;English_United States.1252' LC_CTYPE = 'English_United States.1252'; also throws error ERROR: invalid locale name: "English_United States.1252" So it looks like pg_dump/pg_restore with --create works only from Linux to Windows and does not work from Windows to Linux. I expect that it should work from Windows to Linux also. Andrus.

Using compression on TCP transfer

2020-03-31 Thread Andrus
commands are sent and results are retrieved. Clients have 10-20Mbit download speeds, and 5-20 Mbit upload speeds. Will data compression increase speed ? If yes, how to implement this ? Andrus.

Re: Using compression on TCP transfer

2020-03-31 Thread Andrus
database=mydb SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off) Maybe because according to TLSv1.3 standard, compression is no more allowed or psqlodbc does not pass it to pglib. How to compress ? Andrus.

Re: Using compression on TCP transfer

2020-03-31 Thread Andrus
upport compression or not. Andrus.

Re: Using compression on TCP transfer

2020-03-31 Thread Andrus
tgres. Andrus.

Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12

2020-04-02 Thread Andrus
stgres 9.1 it worked. Andrus.

Re: Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12

2020-04-02 Thread Andrus
rror at or near "[" LINE 3: .../BuyerParty/ContactData/ContactFirstName/text()',x)[1]::text How to fix ? Posted also in https://stackoverflow.com/questions/60993975/fixing-set-returning-functions-are-not-allowed-in-update Andrus.

Why there is 30000 rows is sample

2020-04-04 Thread Andrus
ables ? Is 3 some magical value, how to control it. Using Postgres 12 in Debian. Andrus.

Re: Using compression on TCP transfer

2020-04-04 Thread Andrus
gger does not fix this. How to fix this without re-writing huge number of sql commands? Andrus.

Re: Using compression on TCP transfer

2020-04-05 Thread Andrus
the application. How you provide sample code how to create view or othe method test so that my select statement returns data. Currently select in code create table test ( test varchar ); insert into test values ('test'); select * from test where test ='test '; -- note trailing space does not return data. Andrus.

Server with hot standby slave wont start after vacuum

2020-04-05 Thread Andrus
files by vacuum. Postgres 12 in Debian is used. Andrus.

How to prevent master server crash if hot standby stops

2020-04-06 Thread Andrus
creation (and maybe stop or disable replication slot). Postgres 12 in Debian 10 is used. Andrus.

Re: How to prevent master server crash if hot standby stops

2020-04-06 Thread Andrus
ists. How about implementing"data_reserved_space" setting ? How to create procedure in server or maybe cron scipt which stops replication if disk becomes nearly full ? How to force to stop replication slot in master if it is in use ? Andrus.

Re: How to prevent master server crash if hot standby stops

2020-04-06 Thread Andrus
worker (for the slot monitoring as well as optionally killing and/or dropping), a simple cron job or even check_postgres. Where to find some sample how to implement this ? I read from docs that slot cannot dropped if it is in use. How to stop replication in this case. Andrus.

Re: How to prevent master server crash if hot standby stops

2020-04-06 Thread Andrus
2A200A2 000102A200B3 000102A20081 000102A20092 000102A200A3 000102A200B4 000102A20082 000102A20093 000102A200A4 000102A200B5 Andrus.

Re: How to prevent master server crash if hot standby stops

2020-04-07 Thread Andrus
Hi! About your third question, you *never* manually mess with the files in pg_wal. The server does that. Is it OK to stop server, delete all files in pg_wal directory and re-start server ? Or should default value put back and wait until server frees 1 GB disk space ? Andrus.

Performance degradation if query returns no rows and column expression is used after upgrading to 12

2020-04-07 Thread Andrus
ows=0 loops=2)" " Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~* '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR (markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig, (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR (to_tsvector('english'::regconfig, (engnimetus)::text) @@ plainto_tsquery('testmiin'::text)))" "Rows Removed by Filter: 7202" "Planning Time: 1.729 ms" "Execution Time: 185.674 ms" If there are more column expressions, perfomance difference is bigger. rid table used in column expression contains 1.8 million of rows. Performance degradation probably occured if upgraded from Postgres 9.1 to Postgres 12 Since no data is returned query perfomance should be same. How to fix it ? Andrus.

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

2020-04-07 Thread Andrus
variation of this query running with diferent data in different database in same Debian 10 server. It works fast. Should I disable jit only for this query or in postgresql.conf permanently? Andrus.

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

2020-04-07 Thread Andrus
Hi! >It is really strange why it is too slow. Can you prepare test case? Looks like >bug (maybe not Postgres's bug) Testcase is below. With jit on it takes 3.3 sec and with jit off 1.5 sec. Andrus. create temp table toode ( toode char(20), ribak

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

2020-04-08 Thread Andrus
7; or toode.toode ilike '%'||'testmiin'||'%' escape '!' or toode.markused ilike '%'||'testmiin'||'%' escape '!' or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin') or to_tsvector('english',toode.engnimetus) @@ plainto_tsquery('testmiin') Andrus.

Huge number of pg_temp and pg_toast_temp schemas

2020-04-10 Thread Andrus
Debian 10 is used. Andrus.

Re: Huge number of pg_temp and pg_toast_temp schemas

2020-04-10 Thread Andrus
. I turned most of Nodes options in PgAdmin options off but pgadmin still shows them. It shows also pg_toast schema. Andrus.

Forcibly disconnect users from one database

2020-04-10 Thread Andrus
by database name but those disappeared in pgAdmin 4. How to find which users are using database and forcibly disconnect them ? Andrus.

Which commands are guaranteed to drop role

2020-04-11 Thread Andrus
ete"; drop role "roletodelete"; But got error ERROR: role "roletodelete" cannot be dropped because some objects depend on it DETAIL: privileges for schema public; How to create script which is guaranteed to delete role ? This script already contains: revoke all on schema public,firma1 from "roletodelete" cascade; Why postgres complains that privileges for schema public depend on this role if they are revoked ? How to fix this? Andrus

Re: Which commands are guaranteed to drop role

2020-04-12 Thread Andrus
ork in Postgres 9.0 probably. How to add 9.0+ support for it. Revoking privileges from view in not required in earlier releases. Andrus.

How to restore database to previous state

2020-05-10 Thread Andrus
create standby server which replicates changes using 24 hour delay and data directory backups in every night. However this requires separate delayed standby server . Is there simpler solution, e.q reverse playback of wal segments starting from current data directory contents. Andrus.

Hot and PITR backups in same server

2020-05-17 Thread Andrus
this OK ? Or is some better way to implement hot stadby and PITR possibility in same computer ? Postgres 12 in Debian is used. Andrus.

Re: Hot and PITR backups in same server

2020-05-18 Thread Andrus
r pg_basebackup should saved in backup server and should be used for PITR recovery when needed. How to use hot standby server for this ? Or should separate cluster in 5433 port created and pg_rewind or something other used for PITR. Andrus.

Re: Hot and PITR backups in same server

2020-05-19 Thread Andrus
hat main server will also stop with "no space left on device" error. Or is there some option like to reserve some disk space or limit wal size so that main server can continue on backup server crash. Andrus.

How to recover from compressed wal archieve in windows

2020-05-20 Thread Andrus
Thi sis probably because %f argument is WAL file name without extension. How to use compressed WAL files for WAL archieve and restore in windows ? Andrus.

Re: How to recover from compressed wal archieve in windows

2020-05-20 Thread Andrus
compress it at some point before it gets restored. How to decompress it automatically in windows ? In Linux restore_command = 'gunzip < "archived_wal/%f" > "%p"' maybe works. Will wal_compression=on will produce compressed wal files to additional compression is not needed? Andrus.

Re: How to recover from compressed wal archieve in windows

2020-05-20 Thread Andrus
r when WAL files are copied to other device, copy is perfomed probably in uncompressed form in windows. How to set compressed attribute to pg_wal directory in Linux ext4 file system ? Andrus.

Query returns no rows in pg_basebackup cluster

2020-05-21 Thread Andrus
indows also? Database in Windows is in read-only (recovery) mode so it cannot changed. Postgres 12 is used. Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-21 Thread Andrus
Hi! Are you referring to two different instances of Postgres on Windows? No. Main server is in Linux and backup server is in windows. Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-21 Thread Andrus
, liigid COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default; Maybe it is possible to force postgres in windows to use the same locale as in Linux. Locales are actually the same. Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-21 Thread Andrus
-only database also, e-q forcing same local in postgres.conf Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-21 Thread Andrus
may occur ? Will pg_dump/pg_restore in Windows server fix all issues. Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-21 Thread Andrus
pg_basebackup . Can this backup used for PITR in Linux ? Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-22 Thread Andrus
QL state: XX002 Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-22 Thread Andrus
alter table desktop alter baas type char(8) collate ucs_basic fixes the issue. Is this fix reasonable ? What other issues may occur ? Can base backup created in windows using pg_basecakup used in Linux without such fix? Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-24 Thread Andrus
(1) https://www.2ndquadrant.com/en/blog/icu-support-postgresql-10/ Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-24 Thread Andrus
when new database is added or new tables are added to database they will start replicate automatically ? Will it require more powerful backup server to replay main server sql stream from different databases. Andrus.

Re: Query returns no rows in pg_basebackup cluster

2020-05-25 Thread Andrus
specify recovery target time for applying sql statements to base backup. Is PITR recovery supported only using binary WAL files ? Other limits can probably be solved. Andrus.

How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-05-31 Thread Andrus
wal_keep_segments= 360 # was 180 Will this allow replication to start after pg_basebackup ? According to doc min_wal_size and wal_keep_segments both keep the minimum number of wal segments for replication. Why those parameters are duplicated? Andrus.

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-05-31 Thread Andrus
Hi! On 31. May, 2020, at 21:47, Andrus wrote: replikaator@[unknown] ERROR: requested WAL segment 000102CF00E9 has already been removed the message says it all. You need to copy the WAL file 000102CF00E9 and newer to the replica's pg_wal directory because it has

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-05-31 Thread Andrus
by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. Will wal_keep_segments keep segments also if named replication slot is lot used ? Andrus.

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-05-31 Thread Andrus
--recursive --verbose 0700 /var/lib/postgresql/12/main chown -Rv postgres:postgres /var/lib/postgresql/12/main /etc/init.d/postgresql start In other words why do a pg_basebackup if you have a standby receiving WALs? I dont receive WALs. Andrus.

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-05-31 Thread Andrus
/12/main chmod --recursive --verbose 0700 /var/lib/postgresql/12/main chown -Rv postgres:postgres /var/lib/postgresql/12/main /etc/init.d/postgresql start Andrus.

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-05-31 Thread Andrus
up so that cluster is tranferred over internet faster? Maybe it can transferred in compressed form over internet. Andrus.

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-06-01 Thread Andrus
postgres /var/lib/postgresql/12/main /etc/init.d/postgresql start How to create replication server ? Andrus.

Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

2020-06-01 Thread Andrus
s", not as "root". I tried sudo --user=postgres pg_basebackup but got error could not change directory to "/root": Permission denied Andrus.

canceling statement due to conflict with recovery after pg_basebackup

2020-06-03 Thread Andrus
t.oid = c.reltablespace where (pg_total_relation_size(c.oid)>>21)>0 and c.relkind!='t' order by pg_total_relation_size(c.oid) desc Andrus.

How to get previous log file

2020-06-28 Thread Andrus
-2020-06-19_00.log How get yesterday log file from remote client application using postgresql query ? Using Postgres 12 in Debian. Andrus.

How to create function returning numeric from string containing percent character

2020-07-22 Thread Andrus
acter, select val('1,2%') returns 0. How to force it to return 1.2 ? It should work starting from Postgres 9.0 Posted also in https://stackoverflow.com/questions/63032072/how-to-create-function-returning-value-up-to-first-non-digit-decimal-charcater#63032126 Andrus.

How to distribute products to shop by amount of sales

2020-08-15 Thread Andrus
d products should moved first. Only one product (quantity 1) should moved from each product code. Using data abouve, there should be 3 shoes (producttype.desired) in shop but are only 2 (sum(shop.quantity) for shoes). Most sold shoe in warehouse is SHOE2 So SHOE2 should be moved to shop from war

Include 3 previous tokens in syntax error message

2020-12-27 Thread Andrus
makes this different from source position. How to fix this so that 3 last tokens are returned in message like Syntax error at or near ' i > )' Andrus.

duplicate key value violates unique constraint pg_default_acl_role_nsp_obj_index

2021-01-05 Thread Andrus
ot;testuser"; revoke all on  kasutaja,kaspriv,logifail from "testuser" cascade; grant select on kaspriv,kasutaja to "testuser"; grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup,kasilfirma) on kasutaja to "testuser"; grant insert on logifail to "testuser"; Using PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit Andrus.

Error messages on duplicate schema names

2021-01-06 Thread Andrus
"pg_default_acl_role_nsp_obj_index"DETAIL: Key (defaclrole, defaclnamespace, defaclobjtype)=(30152, 186783649, r) already exists. Should duplicate schema names accepted or should their usage throw better error messages. Andrus.

REASSIGN OWNED BY in current database only

2021-01-14 Thread Andrus
owner unchanged ? Andrus.

permission denied for large object 200936761

2021-02-01 Thread Andrus
w to find and delete all large objects in database ? Maybe it is created accidently . Using  PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit Andrus.

Re: permission denied for large object 200936761

2021-02-01 Thread Andrus
firma74 command line options are used. You can query that to see what is there. I would not go about deleting until you find what the large objects are for. select * from pg_largeobject returns empty table. Database has approx 50 schemas and many thousands of tables. Andrus.

Re: permission denied for large object 200936761

2021-02-01 Thread Andrus
https://www.postgresql.org/docs/12/catalog-pg-largeobject-metadata.html instead. select * from  pg_largeobject_metadata returns 3 rows: Oid  Lomowner   200936761  30152   200936762  30152   200936767   30152 How to find table and schema which is referenced by this ? Andrus.

  1   2   >