Re: Creating A GIN index on JSONB column (large database)

800GB/20TB = 4%. You've got a 20TB database, and run out of space when *4% is added*. Either you're running way to close to the edge, or I misunderstand something. Either way, enable auto-scaling. https://aws.amazon.com/about-aws/whats-new/2019/06/rds-storage-auto-scaling/ On 8/9/22 10:29,

Re: Would it be possible to add functions to tab-completion in psql?

On 8/16/22 07:28, hubert depesz lubaczewski wrote: Hi, As a dba I have to, very often, query system functions, starting with pg_last_xact_replay_timestamp and pg_current_wal_lsn. Would it be possible/hard/expensive, to change tab-completion so that: select pg_ would work? It does, in the psql

Re: Would it be possible to add functions to tab-completion in psql?

On 8/16/22 08:01, hubert depesz lubaczewski wrote: On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote: On 8/16/22 07:28, hubert depesz lubaczewski wrote: Hi, As a dba I have to, very often, query system functions, starting with pg_last_xact_replay_timestamp and pg_current_wal_lsn. Would it

Re: Regarding availability of 32bit client drivers for postgresql 13/14

WHY 32-bit in 2022 under RHEL 8.5? On 8/16/22 23:53, Aravind Phaneendra wrote: Thank you Adrian for the response, We are looking for 32bit client drivers for RHEL 8.5. An another question .. How does the enterprise customers using PostgreSQL can subscribe to official support ? Thanks & Reg

Re: Setting up streaming replication on large database (20+ TB) for the first time

pg_backrest will certainly backup your data faster. It might be able to be used as a seed instead of pg_basebackup. On 8/17/22 15:06, Ivan N. Ivanov wrote: I have a large database (~25 TB) and I want to set up streaming replication for the first time. My problem is that after completion of th

Re: Is it possible to keep indexes on different disk location?

On 8/18/22 14:54, W.P. wrote: [snip] But You are right, I am taking backups  irregular. cron job. -- Angular momentum makes the world go 'round.

Re: Setting up a server with previous day data

On 7/19/22 02:22, Srinivasa T N wrote: Hi All,    I have a primary postgresql 12 server which is being continuously used for transaction processing.  For reporting purposes, I want to set up a secondary server which has got previous day data.  Everyday night, I want the data from primary to be

Re: Setting up a server with previous day data

On 8/24/22 01:42, Peter J. Holzer wrote: On 2022-08-23 19:15:58 -0500, Ron wrote: That was before someone developed a utility to convert the roll-forward logs into INSERT, UPDATE and DELETE statements. Such a utility for PostgreSQL that would convert yesterday's WAL files into SQL would r

Re: Restriction on table partition expressions

It certainly does make sense to have global indices on partitioned tables.  Rdd/VMS had them 20+ years ago, and they are (I still have two production systems using Rdb on OpenVMS) darned useful. Did it require dropping the index before dropping a partition? Absolutely!!  But *of course* /every

Re: Vacuum Full is not returning space to OS

On 9/1/22 04:47, Sushant Postgres wrote: Hi All, I am running Azure PostgreSQL database version 11 with replication enabled. Azure Postgresql is sufficiently different from Vanilla that the standard answer is "ask AWS". But the autovacuum is not happening. Even Vacuum full is also not recl

Re: Vacuum Full is not returning space to OS

On 9/1/22 07:01, Daniel Gustafsson wrote: On 1 Sep 2022, at 13:57, Ron wrote: On 9/1/22 04:47, Sushant Postgres wrote: I am running Azure PostgreSQL database version 11 with replication enabled. Azure Postgresql is sufficiently different from Vanilla that the standard answer is "as

Re: View definition changes after reloading pg_dump export

On 9/1/22 09:08, Tom Lane wrote: Wesley Schwengle writes: There is a view that we create and it uses a function and the view definition changes between runs. I'm not sure why this is happening, does someone know? The core reason for the discrepancy is that the parser inserts implicit coerci

Re: Determine if a user and database are available

On 9/2/22 17:21, Tom Lane wrote: Jeffrey Walton writes: I have another beginner question. I am trying to use pg_isready to determine if a database and user are present. The program seems to always succeed, even when I delete the user or the database. That's a feature actually. The intended us

Re: Feature proposal: immutable/sealed partitions (and maybe tables, too)

By "SEALED", do you mean "READ ONLY"? On 9/6/22 14:39, Levi Aul wrote: My company runs some large OLAP data warehouses with append-only, time-partitioned datasets. Our workloads involve aggregations and joins, and query the data in ways not amenable to constraint-exclusion; and we serve a high

Re: pgBackRest on MacOS

On 9/10/22 07:31, Marc wrote: Has anybody pgbackrest running on MacOS ( Monterey ? ) If yes are you willing to share the how to ? Or can anybody guide us to an “easy” how to ? It's written in Perl, so installation from source should be easy. https://github.com/pgbackrest/pgbackrest/tree/main

Re: toast useless

On 9/13/22 08:18, Simon Riggs wrote: On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro wrote: What problem do they cause you? They don't cause any problem, I was just trying to get my database as clean as possible. I have some thousands of these toast tables with 8k bytes, so I was trying to e

Re: CVE-2022-2625

Software is only certified for 9.5?  Hopefully you're running 9.5.25. I feel your pain... we've got some databases that will stay at 9.6 for another year. On 9/14/22 23:24, misha1966 misha1966 wrote: All business processes are hooked on postgresql 9.5. There is no way to update. Unfortunatel

Re: CVE-2022-2625

There are nine months of bug fixes. On 9/15/22 09:52, misha1966 misha1966 wrote: Is there a patch for 9.6 ? Четверг, 15 сентября 2022, 17:55 +09:00 от Ron : Software is only certified for 9.5?  Hopefully you're running 9.5.25. I feel your pain... we've got some databases

Re: CVE-2022-2625

On 9/15/22 10:19, Tom Lane wrote: =?UTF-8?B?bWlzaGExOTY2IG1pc2hhMTk2Ng==?= writes: Is there a patch for 9.6 ? No; that's out of support too. You might find that adapting the v10 patch back to 9.6, and thence to 9.5, would be easier than trying to do it in one step. I'm a little bemused by yo

Re: Query Performance

On 9/17/22 00:28, sivapostg...@yahoo.com wrote: Hello, My query is like this Select a.field1, a.field2, a.field3 From   (Select a.field1, b.field2, c.field3         From   table1 a         Join   table2 b         on     b.something = a.something         Join   table3 c         On     c.somethin

Re: PCI-DSS Requirements

On 9/20/22 04:27, Inzamam Shafiq wrote: Hi Team, Anyone on PCI-DSS requirements for PostgreSQL DB, need help for some of the points. Can you be more specific?  (Typically. the auditors or the "audit pre-check" team will ask for a bunch of details on how your instance is configured.) Th

Experience with Dell SRM/SRDF?

Has anyone used SRM/SRDF to replicate a Linux VM running PostgreSQL? If so, did you have any problems when "flipping" to the DR replica? I don't think PostgreSQL will care or notice, as long as data/base and data/pg_xlog are on the same replicated LUN, but wanted to ask for others' experience.

Re: PCI-DSS Requirements

We use PgBackRest to create encrypted backups, but the nature of pg_dump means that the only way for them to be encrypted is to add that feature to pg_dump. On 9/22/22 01:16, Inzamam Shafiq wrote: Hi Ron, Thank you for the response. Actually we are in a starting phase and I have done

pg_dump failed with error code 255, but I don't see why

v9.6.2.23 I always run pg_dump with the --verbose option, and the bash script echos $?, so there's a 25,664 line log file for me to examine which explicitly shows the return code. But however much I search the log file for strings like ERROR, FATAL, WARN, INFO, and "archiver", I don't see

Re: pg_dump failed with error code 255, but I don't see why

On 9/22/22 09:34, Tom Lane wrote: Ron writes: v9.6.2.23 I always run pg_dump with the --verbose option, and the bash script echos $?, so there's a 25,664 line log file for me to examine which explicitly shows the return code. A quick search of the 9.6 pg_dump source code shows that it

Validate the internal consistency of pg_dump output?

In my case, it's directory format backups. (I'm not asking whether the pg_dump output is exactly the same as the data in the database; that can only be done inside of  pg_dump, and I don't see any option for that anyway.) Is there a tool for validating that: - there are "\N" lines at the end

Re: Postgresql database and application server compatibility

Pg13 listens on port 5432 just like Pg 9.6, if that's what you mean. On 9/29/22 20:36, Rickson Marshall wrote: Hello Postgresql community, My name is Rickson. I hope this email finds you well and I hope that I’m asking this question in the right forum. If this is not the right mailing list,

Singleton SELECT inside cursor loop

AWS RDS Postgresql 12.11 It runs quickly, no matter what the fetched cursor values are, as long as the cursor returns less than 8 rows.  After that, the singleton SELECT's performance tanks. We've got plenty of other procedures which have a similar "Singleton SELECT inside a cursor" design (

Example code Re: Singleton SELECT inside cursor loop

5639 ---    498 (1 row) Time: 459.724 ms sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31'); task001785639 ---    498 (1 row) Time: 461.155 ms sides=> select strans.TASK001785639(1, 507650, '2022-08-01&#

Re: Example code Re: Singleton SELECT inside cursor loop

On 10/1/22 07:20, Peter J. Holzer wrote: On 2022-10-01 02:22:22 -0500, Ron wrote: I extracted the singleton into a function, and that helped me isolate the problem. I don't know what a singleton select is (duckduckgo tells me it has something to do with DB2 and/or COBOL, or maybe it'

Re: Example code Re: Singleton SELECT inside cursor loop

On 10/1/22 07:21, Christoph Moench-Tegeder wrote: ## Ron (ronljohnso...@gmail.com): Note how quickly it runs the first five times, but takes 780x longer the sixth time I run it.  Exiting psql and entering again causes the same slowness the sixth time it's run. Tanks at the sixth time?

Re: Example code Re: Singleton SELECT inside cursor loop

On 10/1/22 14:54, Christoph Moench-Tegeder wrote: ## Ron (ronljohnso...@gmail.com): The question then is "why am I just now seeing the problem?"  We've been using v12 for two years, and it just happened. The only recent change is that I upgraded it from RDS 12.10 to 12.11 a

Re: Example code Re: Singleton SELECT inside cursor loop

On 10/1/22 15:42, Ron wrote: On 10/1/22 14:54, Christoph Moench-Tegeder wrote: ## Ron (ronljohnso...@gmail.com): The question then is "why am I just now seeing the problem?"  We've been using v12 for two years, and it just happened. The only recent change is that I upgraded it

fully qualified domain names and .pgpass

Sometimes (both interactively and via script) I access a remote Pg server via just the bare host name "foobar", and other times via the FQDN "foobar.example.com". I've only been able to get this to work by having two lines in the .pgpass file: foobar:5432:postgres:Allegedly.Strong.Password

Re: fully qualified domain names and .pgpass

On 10/4/22 12:33, Alvaro Herrera wrote: On 2022-Oct-04, Ron wrote: Sometimes (both interactively and via script) I access a remote Pg server via just the bare host name "foobar", and other times via the FQDN "foobar.example.com". I've only been able to get this to wo

pg_restore creates public schema?

pg_dump 9.6.24 pg_restore 13.8 Why does pg_restore explicitly create "public" even though public is automatically created when the database is created? I noticed that when using "--exit-on-error".  It's disappointing, because I had to remove that option, which caused the restore to ignore oth

Re: pg_restore creates public schema?

On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an EOL cluster, and restoring to a supported version. Why does pg_restore explicitly create "public" e

Re: pg_restore creates public schema?

On 10/6/22 10:20, Tom Lane wrote: Ron writes: On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an EOL cluster, and restoring to a supported version. But why are you

Re: pg_restore creates public schema?

On 10/6/22 11:48, Christophe Pettus wrote: On Oct 6, 2022, at 09:46, Ron wrote: Because installing new software on production servers requires hurdles (Service Now change ticket approved by the application support manager, Delivery Service Manager, Engineering Change Board, and a one week

Re: pg_restore creates public schema?

On 10/6/22 12:46, Christophe Pettus wrote: On Oct 6, 2022, at 10:44, Ron wrote: Sadly, that VM doesn't have nearly enough disk space to hold the backup folder. Use file mode, and stream the output via scp/ssh to a different machine? I thought of that, too.  Unfortunately, the ssh versi

Re: pg_restore creates public schema?

On 10/6/22 14:32, Adrian Klaver wrote: On 10/6/22 10:46, Christophe Pettus wrote: On Oct 6, 2022, at 10:44, Ron wrote: Sadly, that VM doesn't have nearly enough disk space to hold the backup folder. Use file mode, and stream the output via scp/ssh to a different machine? Or Plan

Re: pg_restore creates public schema?

On 10/6/22 14:35, Adrian Klaver wrote: On 10/6/22 09:46, Ron wrote: On 10/6/22 10:20, Tom Lane wrote: Ron writes: On 10/6/22 09:49, Tom Lane wrote: Ron writes: pg_dump 9.6.24 You realize that that version's been out of support for a year? Yes, which is why I'm dumping from an E

Re: pg_restore creates public schema?

On 10/6/22 17:01, Adrian Klaver wrote: On 10/6/22 2:03 PM, Ron wrote: On 10/6/22 14:35, Adrian Klaver wrote: On 10/6/22 09:46, Ron wrote: On 10/6/22 10:20, Tom Lane wrote: Because installing new software on production servers requires hurdles (Service Now change ticket approved by the

Re: pg_restore creates public schema?

On 10/7/22 15:31, Alban Hertroys wrote: On 6 Oct 2022, at 22:52, Ron wrote: On 10/6/22 12:46, Christophe Pettus wrote: On Oct 6, 2022, at 10:44, Ron wrote: Sadly, that VM doesn't have nearly enough disk space to hold the backup folder. Use file mode, and stream the output via scp/ssh

Re: Cannot convert partitioned table to a view

On 10/7/22 15:27, Kouber Saparev wrote: На чт, 6.10.2022 г. в 17:02 ч. Tom Lane написа: That is an ancient backwards-compatibility hack that you should not rely on, and most certainly shouldn't try to make use of from user code.  It exists because very ancient versions of pg_dump di

Re: Different execution plan between PostgreSQL 8.4 and 12.11

Did you analyze and vacuum all of the tables in the new database? On 10/9/22 04:11, gzh wrote: Hi, I have had a Perl Website working for 7 years and have had no problems until a few weeks ago I replaced my database server with a newer one. Database server (old): PostgreSQL 8.4 32bit Datab

Re: Same query, same data different plan

How identical is "identical"? For example, does diff says that "pg_dump --schema-only" of DB1 and DB2 are perfectly identical? And are the table counts identical? On 10/10/22 08:15, Kostas Papadopoulos wrote: Hi, Thank you for responding. My question is not about the performance of a speci

Re: Weird planner issue on a standby

On 10/11/22 22:35, Julien Rouhaud wrote: On Tue, Oct 11, 2022 at 07:42:55PM +0200, Guillaume Lelarge wrote: Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera a écrit : On 2022-Oct-11, Tom Lane wrote: Are there any tables in this query where extremal values of the join key are likely to be in rec

Re: Is this error expected ?

On 10/19/22 01:50, Yavuz TANRIVERDİ wrote: |Hi,| |i have an | | |"ERROR: UNION types "char" and text cannot be matched CASE WHEN indisprimary THEN"|| |error from | |https://github.com/yiisoft/yii/blob/e7c298343bf1f76186d443b62ff853d2d36e19f0/framework/db/schema/pgsql/CPgsqlSchema.php#L233| |I r

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

On 10/19/22 08:06, Mladen Gogala wrote: [snip] Applications do this kind of thing all the time, very successfully; it was just that the loop was in the application rather than in the procedure. High commit rates happen all the time, and they don't break PostgreSQL.  For example, an IoT app

Re: Speeding up adding fky on a very large table

On 10/19/22 08:31, Ravi Krishna wrote: AWS Aurora based on PG 13. Large partitioned table of 5+ billion rows and 7TB in size. ALTER TABLE abc ADD CONSTRAINT fk_123 FOREIGN KEY (a,b,c) REFERENCES xyz(1,2,3); It seems this is not parallelized. Is there a way. Or directly going into each part

Re: How to store "blobs" efficiently for small and large sizes, with random access

On 10/19/22 04:47, Dominique Devienne wrote: [snip] PS: Another drawback of lo is that because it's a single table, it still subject to the 32 TB limit on a relation. The 4TB limit per lo is way more than we need, but the 32 TB limit may actually be more of an issue for our larger clients,

Re: How to store "blobs" efficiently for small and large sizes, with random access

On 10/19/22 06:38, Andreas Joseph Krogh wrote: På onsdag 19. oktober 2022 kl. 13:21:38, skrev Dominique Devienne : On Wed, Oct 19, 2022 at 1:00 PM Andreas Joseph Krogh wrote: > Ok, just something to think about; Thank you. I do appreciate the feedback. > Will your databas

pg_restore 12 "permission denied for schema" errors

RDS Postgresql 12.11 Backed up a database from one RDS instance, and now am trying to restore it to a new instance.  (If you're wondering why, it's to restore in an instance with less disk space.) Here are the commands: export PGHOST=${RDSENV}..us-east-1.rds.amazonaws.com cd /mig

Re: How to store "blobs" efficiently for small and large sizes, with random access

On 10/20/22 03:32, Dominique Devienne wrote: [snip] And from the posts here, the issues with large blobs may be more related to backup/restore perhaps, than runtime performance. From my long experience as a first a programmer and then a database administrator, backups and archiving are at the

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far as I know your pg_restore command would import the data into template1

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

On 10/20/22 12:19, Vince McMahon wrote: In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth? There's no pre-allocation in Postgresql.  When you run CREATE TABLE, an (I t

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

On 10/20/22 12:31, Rob Sargent wrote: On 10/20/22 11:29, Ron wrote: On 10/20/22 12:19, Vince McMahon wrote: In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth

Re: pg_restore 12 "permission denied for schema" errors

On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: Frank Gard writes: Am 20.10.22 um 02:58 schrieb Ron: pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB What database do you want to restore your data into? As far

Re: Asking for existence of a GUI frame work similar to Oracle APEX for PostgreSQL

On 10/21/22 12:13, Dionisis Kontominas wrote: Hello all,     This is my fist email and I ask for leniency on the response. I am quite new to PostgreSQL, and had my first taste of a Windows version. My background is Oracle and I have some questions. I will also post in the admin list. 1. Wh

Re: pg_restore 12 "permission denied for schema" errors

On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I ran "pg_dumpall --globals-only --no-role-passwords" on the source instance, and applied it to the ne

Re: pg_restore 12 "permission denied for schema" errors

On 10/22/22 12:00, Adrian Klaver wrote: On 10/22/22 09:41, Ron wrote: On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I was afraid you were going to say that

Re: pg_restore 12 "permission denied for schema" errors

On 10/22/22 16:29, Adrian Klaver wrote: On 10/22/22 14:02, Ron wrote: On 10/22/22 12:00, Adrian Klaver wrote: On 10/22/22 09:41, Ron wrote: On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20

Re: pg_restore 12 "permission denied for schema" errors

On 10/22/22 17:06, Adrian Klaver wrote: On 10/22/22 14:45, Ron wrote: On 10/22/22 16:29, Adrian Klaver wrote: To pseudo for me. What file exactly is: pg_restore --jobs=X --no-owner $NEWDB restoring? And how was that file created? Knowing this might help get at why the more straight

Re: How to remove the partition from table .

On 10/25/22 05:47, Rakesh Nashine wrote: We would like to remove the partition from one of the tables , although that partition has some business data. Now business doesn't need that partition any more .. How could we remove it? although they need the data . That depends on which version of Po

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 10/27/22 07:07, Kristjan Mustkivi wrote: On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: We use dockerized postgres. So that means you aren't just replacing PostgreSQL, but your complete OS (except the kernel). What is the sour

Re: Autovacuum on Partitioned Tables

On 10/31/22 17:26, Ryan Ruenroeng wrote: Hello there! There is a statement in the Postgres Docs: "Partitioned tables are not processed by autovacuum." What does the above statement mean? Does autovacuum not process both the parent and the child tables in a partition relationship? Partition

Re: Autovacuum on Partitioned Tables

On 10/31/22 23:05, Tom Lane wrote: [snip] TBH, if you've got 50m rows, I'm not sure you need partitions at all. Big rows (i.e. document storage tables with bytea or xml fields) can make databases explode in size even with only 50M rows. (Yes, I know the arguments against it, but it works qui

Re: Autovacuum on Partitioned Tables

> <https://www.facebook.com/ryan.ruenroeng> <https://www.linkedin.com/in/ryan-ruenroeng> On Tue, Nov 1, 2022 at 2:54 AM Ron wrote: On 10/31/22 23:05, Tom Lane wrote: [snip] > TBH, if you've got 50m rows, I'm not sure you need partitions at all. Big

SSL/TLS encryption without

AWS RDS Postgresql 12.11 sides=> select * from pg_stat_ssl where pid = 362; -[ RECORD 1 ]-+ pid   | 362 ssl   | t version   | TLSv1.2 cipher    | ECDHE-RSA-AES256-GCM-SHA384 bits  | 256 compression   | f client_dn | client_serial | i

Re: Reducing bandwidth usage of database replication

On 11/2/22 09:56, Sascha Zenglein wrote: Hi all, I want to use the postgres-native logical replication to have multiple clients receive and send data to a central database. Real-time is far less important than network usage, and with my current test setup it appears both instances communicate

Re: unable to install postgreql 13.4

On 11/3/22 01:48, shashidhar Reddy wrote: Hello, I need to install postgresql 13.4 on development server , but getting below errors, please help to resolve this sudo apt-get install postgresql-13.4 Reading package lists... Done Building dependency tree Reading state information... Done E: Un

Re: pg_restore error on function

On 11/3/22 09:28, Post Gresql wrote: Hello I first successfully ran pg_dump -U postgres -b -E UTF8 -f my_dump --format=custom -n my_schema --no-owner -v my_db but then pg_restore --single-transaction -v -U postgres -O -e -d my_other_db my_dump failed with pg_restore: connecting to databas

Re: pg_restore error on function

On 11/3/22 09:57, Adrian Klaver wrote: On 11/3/22 07:45, Ron wrote: On 11/3/22 09:28, Post Gresql wrote: Hello I first successfully ran pg_dump -U postgres -b -E UTF8 -f my_dump --format=custom -n my_schema --no-owner -v my_db What was the *complete* pg_dump command? I'm going t

Re: shutdown Postgres (standby) host causing timeout on other servers in replication

On 11/3/22 15:55, Joanna Xu wrote: Hi All, We have PostgreSQL master-slave replication configuration with 4 servers. After shutting down one standby server, we found below errors on all other 3 servers and the applications couldn’t access the database due to the timeout. DAO-2 datasour

Re: Postgres to edb AS, need conversion?

On 11/3/22 04:44, milist ujang wrote: Hi all, Anyone have experiences or someone from edb can guid about change engine from postgres open source to edb as, di I need to convert the data or simply plug-and-play? Or need run a tool to add catalog table owned by edb as? You'd better ask EDB. -

Re: Setting up replication on Windows, v9.4

On 11/4/22 17:59, Brad White wrote: I'm setting up a backup for our primary postgres server using the archived WAL files. Then I'll try to upgrade it to Streaming Replication. Then I'll upgrade the system to v.latest. For now, we are on v.9.4. FYI: v9.4 has been EOL for 2 years 9 months.   (An

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 08:02, Tom Lane wrote: [snip] call. It'd still be recommendable to pg_dumpall and restore into a freshly-initdb'd cluster, because otherwise you can't be real sure that you identified and cleared all the data corruption. Why *just* pg_dumpall instead of "pg_dumpall --globals-only" an

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

On 11/7/22 09:43, Tom Lane wrote: Ron writes: On 11/7/22 08:02, Tom Lane wrote: call. It'd still be recommendable to pg_dumpall and restore into a freshly-initdb'd cluster, because otherwise you can't be real sure that you identified and cleared all the data corruption. Why *j

Re: copy file from a client app to remote postgres isntance

On 11/7/22 10:57, Вадим Самохин wrote: Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020 ). It boils down to speci

Re: Feature suggestions for backup and replication

On 11/7/22 16:03, da avory wrote: Hi all, not sure whether this is the right list to ask. I do have two (small!?) feature suggestions, both regarding backup and replication, which might appeal to a wider audience. 1) it would be very great to have the option to add a timestamp to all lines

Re: copy file from a client app to remote postgres isntance

On 11/9/22 10:17, Peter J. Holzer wrote: On 2022-11-07 14:40:40 -0600, Ron wrote: On 11/7/22 10:57, Вадим Самохин wrote: I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https

Re: Q: documentation improvement re collation version mismatch

On 11/10/22 02:33, Julien Rouhaud wrote: [snip] For now, the only safe way to go is either reindex everything, or everything except some safe cases (non-partial indexes on plain-non-collatable datatypes only). Usually, those safe cases are usually enough to avoid most of useless reindex activity

Re: Q: documentation improvement re collation version mismatch

On 11/10/22 08:33, Julien Rouhaud wrote: On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote: On 11/10/22 02:33, Julien Rouhaud wrote: [snip] For now, the only safe way to go is either reindex everything, or everything except some safe cases (non-partial indexes on plain-non-collatable

Re: Setting up replication on Windows, v9.4

On 11/10/22 21:24, Brad White wrote: On 11/7/2022 3:42 PM, Rob Sargent wrote: > Care to share some of the ways the app stopped working? You might get a leg up on where best to remediate. I don't recall, as that was a few months ago. We are running MS-Access as a front end with Postgres as the

Re: Table : Bloat grow high

On 11/11/22 11:09, Alexis Zapata wrote: Hi all, In postgresql 13.5 Upgrade to at least 13.8.  (13.9 was released yesterday.) I have a table (size 3.1 GB) and in this table occurs near to 200 updates per second, after 2 days the size table is 7 GB and bloat grow to 45% and the query operation

Re: Setting up replication on Windows, v9.4

houldn't have had any effect. > Anyway, try Pg 9.6. I have Pg 12 installed, so I'll try that. I'll start a new thread on my adventures in upgrading. Thanks, Brad. On Fri, Nov 11, 2022 at 12:38 PM Ron wrote:    Nothing in the application error logs?  What about the Postg

Re: Upgrading to v12

Step #1: upgrade to 9.4.26.  You'll get *five years* of bug fixes. (If the client lets you, of course.  I had servers stuck on 8.4.17 and 9.2.7 that were only upgraded because PCI auditors were going to tell my client's client, and that scared /my/ client.  Now they're on 9.6.24...) On 11/11/

Re: Table : Bloat grow high

On 11/12/22 08:05, Laurenz Albe wrote: On Fri, 2022-11-11 at 17:09 +, Alexis Zapata wrote: In postgresql 13.5 I have a table (size 3.1 GB) and in this table occurs near to 200 updates per second, after 2 days the size table is 7 GB and bloat grow to 45% and  the query operations are degrad

Re: Upgrading to v12

On 11/11/22 23:09, Adrian Klaver wrote: On 11/11/22 20:59, Brad White wrote: On Fri, Nov 11, 2022, 9:57 PM Adrian Klaver > wrote: Yes. The backup is from production. V9.4 is running on 5432 on all servers. That particular restore happens to be on the dev serve

Re: Setting up replication on Windows, v9.4

Note that WAL replication replicates *the whole instance* not just specific databases.  You need logical replication for that. Also, I just learned that 9.4 *does* have WAL replication slots (which makes replication *much* easier).  v9.6 is where replication is "like rolling off a log" simple.

Re: Calling function from VFP changes character field to Memo

On 11/15/22 12:54, Frank Cazabon wrote: On 15/11/2022 2:44 pm, Tom Lane wrote: Frank Cazabon writes: If however I have a function defined like this CREATE OR REPLACE FUNCTION public.testfunction(       )       RETURNS TABLE       (      Firstname character(30)       )       LANGUAGE 'plp

Re: Drop role cascade ?

On 11/17/22 17:16, Tom Lane wrote: David Gauthier writes: Someone who had privileges in a lot of tables/schemas/DBs left the company. I want to drop that role but "rop role xxx" won't let me because... "xxx cannot be dropped because some objects depend on it". Is there a way to just get rid o

Re: Drop role cascade ?

On 11/17/22 17:57, Tom Lane wrote: Ron writes: On 11/17/22 17:16, Tom Lane wrote: Not directly, but see REASSIGN OWNED and DROP OWNED. SIMULATE and VERBOSE options would be handy, to see what those commands would actually do. Hmph. I'm surprised to realize that those commands don'

Re: Drop role cascade ?

On 11/17/22 17:07, David Gauthier wrote: psql (11.5, server 11.3) on linux Someone who had privileges in a lot of tables/schemas/DBs left the company.  I want to drop that role but "rop role xxx" won't let me because...  "xxx cannot be dropped because some objects depend on it". Is there a w

Re: copy databases from two differend backups to one cluster

On 11/18/22 06:33, Joseph Kennedy wrote: I would like to copy databases from two different pg_basebackup backups to one lab environment database cluster on another server. Is it possible ? It's a good idea to do it in that way. Databases has your oid in pg_basebackup we can find it id in bas

Re: Calculating average block write time

On 11/18/22 09:51, Laurenz Albe wrote: On Fri, 2022-11-18 at 11:11 +0100, Thomas Kellerer wrote: Thomas Kellerer schrieb am 04.11.2022 um 10:19: I can easily calculate the average block read time using pg_stat_database and divide blk_read_time by blks_read. While there is a column blk_write_ti

Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

On 11/18/22 16:13, Peter J. Holzer wrote: [snip] So you can give these credentials to you developers or devops folks (whom you trust not attack the system - They like to "fix" things without documenting what they did, and then, when something breaks, denying they did anything (or honestly not

Re: table inheritance partition and indexes

Out of curiosity, why INHERITS in v13 instead of PARTITION BY? On 11/23/22 09:31, Ted Toth wrote: I've created a table with a number of indexes and then created a partition table that inherits from it using "CREATE TABLE... INHERITS..." . I've then queried pg_indexes on the parent for its index

<    1   2   3   4   5   6   7   8   9   10   >