Re: pg_dump of partitioned table not working.

On 12/2/20 6:21 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:14 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: > It does seem like there might be reason to have a switch along > the lines of "--include-child-tables". That would be great, but wo

Re: pg_dump of partitioned table not working.

On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: That you were comparing apples and oranges - specifically that the database you were du

Re: pg_dump of partitioned table not working.

On 12/2/20 6:54 PM, Adrian Klaver wrote: On 12/2/20 4:38 PM, Ron wrote: On 12/2/20 6:21 PM, Adrian Klaver wrote: On 12/2/20 4:13 PM, Ron wrote: On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: That you were

Re: pg_dump of partitioned table not working.

On 12/2/20 6:14 PM, Ron wrote: On 12/2/20 5:50 PM, Tom Lane wrote: I wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature.  Maybe we should add it. Or actually: that

Re: Alter the column data type of the large data volume table.

On 12/3/20 11:26 AM, Michael Lewis wrote: On Thu, Dec 3, 2020 at 10:18 AM Rich Shepard > wrote: On Thu, 3 Dec 2020, Michael Lewis wrote: > On Wed, Dec 2, 2020 at 11:53 PM charles meng mailto:xly...@gmail.com>> wrote: >> I have a table with 1.6 billi

Re: Alter the column data type of the large data volume table.

On 12/3/20 11:53 AM, Michael Lewis wrote: On Thu, Dec 3, 2020 at 10:18 AM Rich Shepard > wrote: Tell me, please, why ALTER TABLE ALTER COLUMN SET DATA TYPE BIGINT will not do the job? I've found some varchar columns in a couple of tables t

Deferrable FK not behaving as expected.

v12.5 I added a deferrable FK constraint on sales_detail to ensure that no one can delete records from sales_header when an associated sales_detail record exists. That works perfectly. The problem is that try to delete parent records before the child records *inside a transaction* also fail

Re: Deferrable FK not behaving as expected.

On 12/7/20 12:19 PM, Thomas Kellerer wrote: Ron schrieb am 07.12.2020 um 19:15: Referenced by: TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"    FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think

Re: Deferrable FK not behaving as expected.

On 12/7/20 12:19 PM, Thomas Kellerer wrote: Ron schrieb am 07.12.2020 um 19:15: Referenced by: TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"    FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think

Re: Deferrable FK not behaving as expected.

On 12/7/20 1:27 PM, Tom Lane wrote: Ron writes: Neither technique worked. test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; ALTER TABLE Hmm, reproduced here. There seems to be some kind of bug in ALTER CONSTRAINT --- Do I need to

Re: Deferrable FK not behaving as expected.

On 12/7/20 1:27 PM, Tom Lane wrote: Ron writes: Neither technique worked. test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; ALTER TABLE Hmm, reproduced here. There seems to be some kind of bug in ALTER CONSTRAINT --- it looks like

Re: Deferrable FK not behaving as expected.

On 12/7/20 3:40 PM, Tom Lane wrote: Ron writes: That works *sometimes*, but not when we *really* want it to work. test=# begin transaction; BEGIN test=# ALTER TABLE sales_header DETACH PARTITION sales_header_202001; ERROR:  removing partition "sales_header_202001" violates f

Re: Is there a way to dump schema to files on disk and keep them in sync

On 12/12/20 8:58 PM, Tim Uckun wrote: I want to dump my postgres schema to disk in neat directories like pgadmin presents. Then I want to be able to edit the files and sync changes to the database and ideally if changes were made in the database to sync them back to the disk. That could get rea

Re: Optimize query

On 12/15/20 8:17 AM, Yambu wrote: Hi How would you optimize a query with greater than in where clause eg select * from table1 where id > 1000 and there is an index on id column The question as written is unanswerable. - Why do you think it needs to be optimized? - What is the table cardinal

Re: pg_ctl.exe deleted on abrupt shutdown of Windows

On 12/15/20 4:44 AM, Joel Mariadasan (jomariad) wrote: Team, After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. Maybe English is your second language, but "we are seeing" means that Windows regularly shuts down, and deletes pg_ctl.exe every

Re: pg_ctl.exe deleted on abrupt shutdown of Windows

On 12/15/20 11:26 AM, Laurenz Albe wrote: On Tue, 2020-12-15 at 10:00 -0600, Ron wrote: After abrupt shutdown of Windows, we are seeing the pg_ctl.exe file getting deleted automatically. Only pg_ctl.exe gets deleted? Anyway, there's nothing in Postgres that says "delete pg_

Re: Unexpected result count from update statement on partitioned table

Would (task_type in (1,2)) make any logical difference? On 12/18/20 6:11 AM, Craig McIlwee wrote: Despite looking at this query on and off for a couple of days, it wasn't until seeing it in Lauenz's reply that I noticed  a logical issue with the query that changes things a bit.  There should be

Re: How to REMOVE a fillfactor set by accident?

On 12/31/20 2:13 PM, Thorsten Schöning wrote: Hi all, I'm using the GUI tool pgModeler to maintain schemas and at some point in the past it used fillfactor 10 for some indexes by default. That seems to have changed in the meantime and most of my indexes don't have ANY fillfactor set explicitly.

Re: Max# of tablespaces

It's certainly the "I only know bash" method of determining which partition growing the fastest. On 1/5/21 3:55 PM, Thomas Flatley wrote: I don’t, but I didn’t set up the env As far as I can tell, each tablespace is a partition, and I assume they felt this was the best way to perform partitio

Re: Define hash partition for certain column values

On 1/11/21 12:36 AM, Tom Lane wrote: =?utf-8?B?0JPQvtC70YPQsdC10LLQsCDQr9C90LA=?= writes: Hello, I've found in source code that there is a function satisfies_hash_partition(oid, modulus, remainder, column_values[]) which allows to check if the certain column value will be placed in the certa

Re: Accounting for between table correlation

On 1/15/21 9:19 AM, Alexander Stoddard wrote: I am having ongoing trouble with a pair of tables, the design of which is beyond my control. There is a 'primary' table with hundreds of millions of rows. There is then a 'subclass' table ~ 10% of the primary which has additional fields. The table

Re: upgrade postgres 9.5 to 9.6

On 1/15/21 9:17 AM, Thomas Kellerer wrote: [snip] Is there a reason you are upgrading to 9.6 and not 12 or 13? The application software vendor might only certify the application on 9.6. Or /management/, in their infinite "wisdom", might insist on a One-version upgrade. Or 9.6 might be the defa

Re: Do we need a way to moderate mailing lists?

On 1/16/21 3:01 PM, Rob Sargent wrote: Top/Bottom points um, er elided. Mail has always been well threaded, retaining which message lead to which replies.  How did we get away from relying on that (naked posting)? Outlook. -- Angular momentum makes the world go 'round.

Re: System (and DB) upgrade problem., "part 2"

On 1/18/21 2:58 PM, Bruce Momjian wrote: On Mon, Jan 18, 2021 at 09:53:33PM +0100, W.P. wrote: [snip] Ok, so "step-by-step": 1), I copy / move "somewhere" OLD DB files (*/pgsql/data/* for -d option), 2). Do initdb / postgresql-10-setup to create NEW empty base (in /var/lib/pgsql/ or somewhere,

Re: Customer unable to connect on port 5432, Postgres 10.7

On 1/21/21 7:30 PM, Keith Christian wrote: Hello again Postgres friends, Customer is unable to log in to a PG database on 5432 using a Windows client. Output of my test sessions below, I think the problem may be lines 3 - 7 but no idea. Ideas greatly appreciated! Lines 1,2 PG Log entries Lines

Re: Copy & Re-copy of DB

On 1/23/21 6:52 AM, sivapostg...@yahoo.com wrote: We are an ISV.   I agree the risk involved in sharing the data.  Still few of my customers need that facility and are accustomed to it when using SQL Server.   On switch over to PG, I face this issue as a limitation. Need to find and provide a s

Re: ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it

On 1/23/21 2:36 PM, robert rottermann wrote: Hi there I made somehow a mess with my PostgreSQL installation an an ubuntu 18.4 with PostgreSQL 10.0 service postgresql status ● postgresql.service - PostgreSQL RDBMS    Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pres

Question about logically replicating a multi-TB database

https://www.postgresql.org/docs/12/logical-replication-architecture.html "30.5.1. Initial Snapshot The initial data in existing subscribed tables are snapshotted and copied in a parallel instance of a special kind of apply process. This process will create its own temporary replication slot a

Re: Question about logically replicating a multi-TB database

On 1/23/21 5:37 PM, Avinash Kumar wrote: Hi, On Sat, Jan 23, 2021 at 7:28 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: https://www.postgresql.org/docs/12/logical-replication-architecture.html "30.5.1. Initial Snapshot The initial data in existing subscri

Re: CLUSTER, VACUUM, and TABLESPACEs (oh my)

On 1/24/21 7:50 PM, Guillaume Lelarge wrote: Hi, Le lun. 25 janv. 2021 à 01:38, Demitri Muna > a écrit : [snip] * If I have previously run a CLUSTER command on a table, will future VACUUM FULL commands rewrite the table in the order specified in the

Re: Postgres 9.3 service no longer starts on Windows 8.1

Obligatory "you need to upgrade!!!" comment: both Postgres 9.3 and Windows 8.1 are far beyond EOL. (Don't think I'm being high and mighty, since I still must maintain Pg 9.2 and SQL Server 2005, 2008, & 2008R2 servers...) On 1/26/21 8:51 PM, Lisa Ruby wrote: Don't know why this so often happ

Re: How to post to this mailing list from a web based interface

On 1/28/21 9:27 AM, Alvaro Herrera wrote: On 2021-Jan-28, Ravi Krishna wrote: I am planning to switch to a web based tool to read this mailing list. That's great. While reading is easy via web, how do I post a reply from web. Yeah, "how" indeed. Gmail is a web interface... :) -- Angular

Re: PgAdmin 4 GUI not responding

https://www.pgadmin.org/support/list/ On 2/4/21 3:46 AM, Adith Suresh wrote: Hi Team, I have installed postgres 13 on windows server and I am facing an issue while opening PgAdmin 4. The GUI is not responding. I have tried restarting the service, still the issue is persisting. Please provid

Re: vacuumdb not letting me connect to db

On 2/4/21 5:26 AM, Atul Kumar wrote: Hi, I have 160 GB of RAM, postgres 9.6 is running on the server. after upgrade I ran the below command: "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300 --analyze-only after running that command I was not able to connect the database

Re: vacuumdb not letting me connect to db

What about disk IO?  That's what really gets saturated when running 300 threads. On 2/4/21 11:00 AM, Atul Kumar wrote: Hi, The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption) but I don’t understand one thing here that if max_connections is set to 700 then why I am not able

Re: vacuumdb not letting me connect to db

Your problem screams "IO saturation". On 2/4/21 12:07 PM, Atul Kumar wrote: There is no error message, when I try to connect the database while running vacuumdb with 300 jobs, it gets stuck. On Thursday, February 4, 2021, Ravi Krishna > wrote: >The CPU and RAM

Re: vacuumdb not letting me connect to db

Obviously... don't use 300 threads. On 2/5/21 2:15 AM, Atul Kumar wrote: ok, How do I resolve it ? Any suggestions ? On 2/5/21, Ron wrote: Your problem screams "IO saturation". On 2/4/21 12:07 PM, Atul Kumar wrote: There is no error message, when I try to connect the

Re: vacuumdb not letting me connect to db

On 2/5/21 10:22 AM, Rob Sargent wrote: On 2/5/21 9:11 AM, Ron wrote: Obviously... don't use 300 threads. No, no Ron.  Clearly the answer is more CPUs I hope you're being sarcastic. -- Angular momentum makes the world go 'round.

Re: vacuumdb not letting me connect to db

1.5x or *maybe* 2x if the core count is low, and nothing else is running on the system. Note: even during 300 threads, my RAM utilisation is totally normal. Regards Atul On Saturday, February 6, 2021, Gavan Schneider <mailto:list.pg.ga...@pendari.org>> wrote: On 6 Feb 2021,

Re: psql behavior change on upgrade from version 12.x to 13.1

On 2/9/21 1:57 PM, Bryn Llewellyn wrote: [snip] In other words, different from what you see. I'm an ordinary end user. I don't even think expllictly about “building" anything in the PostgreSQL system. I got into this mess (as I believe) because I did this: brew update brew upgrade A colleague

Building lots of indices in parallel

We're migrating a big database (200ish tables 30 of which have bytea or xml fields; the Oracle size is 10TB) to Postgresql 12.5 (RDS, if it matters), and after the loads are completed, it will be time to create the secondary indices (tables currently only have PK constraints), and they of cou

Re: Insert into on conflict, data size upto 3 billion records

On 2/12/21 12:46 PM, Karthik Kumar Kondamudi wrote: Hi, I'm looking for suggestions on how I can improve the performance of the below merge statement, we have a batch process that batch load the data into the _batch tables using Postgres and the task is to update the main target tables if the

ADD FOREIGN KEY fails, but the records exist

Postgresql 12.5 It's a self-referential FK on a single (but partitioned) table.  The ALTER TABLE command fails, but I queried it, and the record that it fails on exists.  I modified the original INITIALLY IMMEDIATE clause to INITIALLY DEFERRED but that did not help. What am I doing wrong? (

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 It's a self-referential FK on a single (but partitioned) table. The ALTER TABLE command fails, but I queried it, and the record that it fails on exists.  I modified the original INITIALLY IMME

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:27 AM, Adrian Klaver wrote: On 2/15/21 8:23 AM, Ron wrote: On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 The error: DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 00:00:00) is not present in table

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:27 AM, Adrian Klaver wrote: On 2/15/21 8:23 AM, Ron wrote: On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 The error: DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 00:00:00) is not present in table

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:58 AM, Adrian Klaver wrote: On 2/15/21 8:55 AM, Ron wrote: On 2/15/21 10:27 AM, Adrian Klaver wrote: On 2/15/21 8:23 AM, Ron wrote: On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 The error: DETAIL:  Key (amended_response_id

Re: Order by not working

What would you tell pgadmin?  "Order *this* *particular* query -- out of all the billion queries I might write -- in *this particular* manner?" No, that's not how things work.  Just add an ORDER BY when you query the table. On 2/16/21 12:48 PM, Dan Nessett wrote: Thanks Peter. The listing of t

Re: Order by not working

SQL is only intuitive to people who've done programming... :) Also, since your table names are only composed of lower case and underscores, the double quotes are not needed. On 2/16/21 1:41 PM, Dan Nessett wrote: Thanks to those who responded. I have solved my problem by noting the advice to

Syntax checking DO blocks and ALTER TABLE statements?

How does one go about syntax checking this? do $$ begin if exists (select 1 from information_schema.table_constraints    where constraint_name = 'error_to_web_service_error') then        raise notice 'EXISTS error_to_web_service_error';     else         ALTER TABLE web_service_error   

Re: Syntax checking DO blocks and ALTER TABLE statements?

On 2/16/21 5:44 PM, Tim Cross wrote: Given the number, I think I would do the same. A good example of why being 'lazy' can be a virtue. Faster and easier to write a procedure to generate dynamic SQL than write out all those alter statements manually or even write it using a scripting language and

Re: Syntax checking DO blocks and ALTER TABLE statements?

On 2/16/21 6:19 PM, Tim Cross wrote: Ron writes: On 2/16/21 5:44 PM, Tim Cross wrote: Given the number, I think I would do the same. A good example of why being 'lazy' can be a virtue. Faster and easier to write a procedure to generate dynamic SQL than write out all those alter

Re: Slow index creation

On 2/16/21 12:30 PM, Paul van der Linden wrote: Hi, I have 2 functions: CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS $func$ DECLARE     retVal text; BEGIN     SELECT       CASE         WHEN a='v1' AND b='b1' THEN 'r1'         WHEN a='v1' THEN 'r2'         ... snip long list contai

Script checking to see what database it's connected to

Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so it fails. Thus, I want to add a b

Re: Script checking to see what database it's connected to

On 2/21/21 5:26 PM, Rob Sargent wrote: On 2/21/21 4:18 PM, Ron wrote: Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of

Re: Script checking to see what database it's connected to

On 2/21/21 5:26 PM, Julien Rouhaud wrote: On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the ma

Re: Script checking to see what database it's connected to

On 2/21/21 6:49 PM, Rob Sargent wrote: Take it up a notch?  Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ I thought of that

Re: Script checking to see what database it's connected to

On 2/21/21 7:19 PM, Julien Rouhaud wrote: [snip] Well, the supported commands did work. You should probably look at https://www.postgresql.org/docs/current/app-psql.html, you'd see that "exit" is not a supported command and you should instead use \q[uit]. I recommend looking at the semantics of

Re: Simple IN vs IN values performace

On 2/22/21 9:00 AM, Oleksandr Voytsekhovskyy wrote: Greetings, We have queries with IN filters with long list of INT values Sometimes, they running extremely slow, and I have found suggestion to use syntax Field IN (VALUES(1465), (1478), ... Instead of Field IN (1465, 1478, ... On some cases

Re: yum update for postgresql rpms

It's standard procedure for package managers to stop daemons before update and then start them back up afterward. On 2/23/21 8:22 AM, Haas, Scott wrote: Tickling this thread to see if I can get a confirmation of the described behavior for rpm update via yum. I do not want to cross-post (but

Re: ERROR: extra data after last expected column

On 3/7/22 17:57, Rob Sargent wrote: On 3/7/22 16:48, scott macri wrote: [snip] It pukes after the 9th column every time no matter what I change. COPY option_details(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y) FROM '/home/dump

Re: Apparently table locks are the key issue to see red flags

On 3/16/22 15:15, Shaozhong SHI wrote: Apparently table locks are the key issue to see red flags. As we observe, every time, red marking occurs, there must be some tables are locked. Where do these red markings occur?  (It is physically impossible for them to occur in Postgresql because Po

Re: How long does iteration over 4-5 million rows usually take?

On 4/1/22 20:34, Shaozhong SHI wrote: I have a script running to iterate over 4-5 million rows.  It keeps showing up in red in PgAdmin.  It remains active. How long does iteration over 4-5 million rows usually take? What /*exactly*/ are you doing? -- Angular momentum makes the world go 'r

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 4/6/22 11:41, W.P. wrote: W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not a "command". PG was working ok, DB on

Re: Could not read block 0 in file

On 4/8/22 04:54, Magnus Hagander wrote: On Fri, Apr 8, 2022 at 11:06 AM wrote: Hi, While we are looking for a suitable backup to recover from, I hope this community may have some other advice on forward steps in case we cannot restore. RCA: Unexpected shutdown due to cr

Re: Long running processes and commit writing to disk

On 4/8/22 11:39, Shaozhong SHI wrote: When long running processes got disrupted, one may not see any expected result. How to make sure that the result of each operation is saved to disk in a loop? That would of course depend on how often you begin and commit transactions. -- Angular momen

Re: FOR integer loop bug?

On 4/8/22 13:07, Adrian Klaver wrote: On 4/8/22 10:58 AM, Pavel Stehule wrote: pá 8. 4. 2022 v 19:56 odesílatel Adrian Klaver Alright so 1...10 is taken as 1::integer .. 0.10::integer CAST results in 0 and ends up as 1..0. This then is covered by: "If the lower bound is greater than the u

Re: Constraint ordering

On 4/9/22 09:42, Perry Smith wrote: I think (hope) I’ve made a bad assumption. I have my DB with one table with two constraint on new entries. The “first” is for the parent and basename be unique. The “second” is that the devno and inode are unique if it is a directory. When I was doing my

Re: About postgresql-db Directory

On 4/28/22 04:39, Junsong Yang wrote: Hi, A directory called postgresql-db appeared after the postgres instance was deleted. I noticed that the mounting point(/var/lib/pgdata)disappeared. But another directory called postgresql-db appeared at /var/lib/postgresql-db which preserved all the dat

Re: Displaying chat by punished users only to themselves (db fiddle attached)

On 5/4/22 09:40, Alexander Farber wrote: Thank you for replying, David! The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries. How do other web sites know to present only "my" data, even though they don't encode "my" user id in the URL? -- Angular mom

Re: pg_dump: VACUUM and REINDEXING

On 5/6/22 21:35, Hasan Marzooq wrote: Hello! I've some questions around Backup & Restore. 1: Is it necessary to perform a VACUUM and REINDEXING operation after restoring the dump from Postgres 9.6 to Postgres 13? The dump size could be 1/2 TB to 1 TB. Perform VACUUM after there have been ma

Re: consistent postgresql snapshot

On 5/11/22 10:41, Zwettler Markus (OIZ) wrote: PostgreSQL12 running on CentOS7 + ext4. Would it be save to do a "fsfreeze –freeze" + disk snapshot + "fsfreeze –unfreeze" of the PGDATA filesystem to get a consistent snapshot? I am wondering as PostgreSQL default blocksize = 8k while ext4 defa

Re: AW: [Extern] Re: consistent postgresql snapshot

I am not sure if freeze/unfreeze will be enough with default values (8k db blocksize vs. 4k fs blocksize). I am quite sure it should be safe when I also change the fs blocksize to 8k because 1 IO = 1 db block = 1 fs block than. Any thoughts? *Von:*Ron *Gesendet:* Donnerstag, 12. Mai 2022

Re: Automatic PK values not added to new rows

On 5/25/22 13:17, Rich Shepard wrote: On Wed, 25 May 2022, David G. Johnston wrote: The value the sequence provides next is wholly independent of everything except the state of the sequence. It doesn’t care how many rows any table, even its owner, has. The very existence of the delete command s

Re: lifetime of the old CTID

On 7/6/22 01:18, Matthias Apitz wrote: [snip] Ofc, each table has its own primary key(s), used for example for the SELECT ctid, * FROM d01buch WHERE ... As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has for each table a so called SYB_IDENTITY_COLUMN which is static for the

Re: More than one Cluster on single server (single instance)

On 7/18/22 02:55, Daulat wrote: Hello Team, We are planning to create multiple clusters on a single server (single instance) with PostgreSQL V.10 to run multiple applications. Version (9,6, 10, 111, 12...) does not matter. I don't know if it is a good idea to use a single machine to run n

Re: More than one Cluster on single server (single instance)

On 7/18/22 04:46, Guillaume Lelarge wrote: Hi, [snip] It would make things easier for you to use a single cluster, and as many databases as you want in this cluster. It would make *PITR* restoration *of a single database* (or small set of databases) impossible (since such backups -- and ther

Re: postgis

You've never shown us *exactly what you did*, along with any *error messages*. On 7/19/22 22:07, Marc Millas wrote: Postgres installed, but not postgis.. which is why I need some help... Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Jul 19, 2022 at

Re: Batch process

On 7/20/22 00:08, Rama Krishnan wrote: Hi All, I am doing purge activity my sales table contains 5M records I am going to delete more than 1 year data (which was 3M) records so it was running more so I want to do batch wise deletion through plsql  created or replace function data_purge()

Re: postgis

such a question on the postgis mail list. But.. I register thru the postgis web site, and didnt get any answer. ... Marc MILLAS Senior Architect +33607850334 www.mokadb.com <http://www.mokadb.com> On Wed, Jul 20, 2022 at 10:25 AM Ron wrote: You've never shown us *exactly wha

Re: Batch process

But the first thing he does is drop test_old. On 7/20/22 09:52, Adrian Klaver wrote: On 7/20/22 01:28, Ron wrote: On 7/20/22 00:08, Rama Krishnan wrote: Hi All, I am doing purge activity my sales table contains 5M records I am going to delete more than 1 year data (which was 3M) records so

Re: Batch process

How will DELETE WHERE sales_id IN (...); , given that test_old has no index? Of course, we don't know if there's an index on sales.bill_date, since OP's only response has been another "tell me how to do it". On 7/20/22 13:32, Adrian Klaver wrote: On 7/20/22 11:1

Re:

On 7/26/22 10:22, Adrian Klaver wrote: On 7/26/22 08:15, Rama Krishnan wrote: Hi Adrian Thanks for your reply, My actual db size was 320G while I am taking custom format and moving into directly S3 it took more than one day so I am trying to use directory format because  parllel option (-j

Re: pg_dump query failed

On 7/28/22 05:08, karol.malinow...@trustedeyes.pl wrote: Hi all, last week backup of my one database stops with error like below: "pg_dump: [archiver (db)] query failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing

Syntax error when combining --set and --command has me stumped

$ alias psql12 alias psql12='/usr/lib/postgresql/12/bin/psql -p5433' This works ask expected: $ psql12 --set num=42 -ac "\echo :num" echo :num 42 And so does this: $ psql12 --set num=42 psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1)) Type "help" for help. postgres=# select :num;  ?column? ---

Re: Syntax error when combining --set and --command has me stumped

On 7/28/22 14:47, David G. Johnston wrote: On Thu, Jul 28, 2022 at 12:40 PM Ron wrote: What secret sauce am I missing to get this to work? Given that the documentation says: "command must be either a command string that is completely parsable by the server (i.e., it contains no

Re: Connecting to postgres on OSX from Swift using PostgresClientKit

On 7/31/22 19:30, howardn...@selestial.com wrote: On 2022-08-01 01:04, Adrian Klaver wrote: On 7/31/22 16:53, howardn...@selestial.com wrote: On 2022-07-31 23:42, Rob Sargent wrote: On 7/31/22 16:40, howardn...@selestial.com wrote: Hi, Wondered if anyone had experience connection to pg14.1

Re: Syntax error when combining --set and --command has me stumped

On 7/29/22 04:05, Gianni Ceccarelli wrote: If you can use bash, or set up some redirections from whatever you're using to execute ``psql``, you can do:: $ psql somedb --set num=42 <<<'select :num' Timing is on. Expanded display is used automatically. Line style is unicode. Border

Copying records from TABLE_A to TABLE_B (in the same database)

AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are:     INSERT INTO table_a SELECT * FROM table_b; and     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);     \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BIN

Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 13:41, Rob Sargent wrote: On 8/2/22 12:37, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are:     INSERT INTO table_a SELECT * FROM table_b; and     \COPY table_a TO '/tmp/table_a.tsv' WI

Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 13:41, Adrian Klaver wrote: On 8/2/22 11:37 AM, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; Argh, I got the tables backwards.  Should be

Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 13:51, Thomas Kellerer wrote: Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_

Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 13:59, Rob Sargent wrote: On 8/2/22 12:51, Thomas Kellerer wrote: Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and

Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

AWS RDS Postgresql 12.10 https://www.postgresql.org/docs/12/sql-createtable.html [quote] |DEFERRABLE| |NOT DEFERRABLE| This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. *Checking of constraints tha

Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

On 8/3/22 20:02, Adrian Klaver wrote: On 8/3/22 17:30, Ron wrote: AWS RDS Postgresql 12.10 https://www.postgresql.org/docs/12/sql-createtable.html [quote] |DEFERRABLE| |NOT DEFERRABLE|     This controls whether the constraint can be deferred. A constraint     that is not deferrable will be

Re: How to choose new master from slaves.?

On 8/4/22 02:43, Sacheen Birhade wrote: Hi All, I have a very basic question about streaming replication feature of Postgres. Let’s assume I have servers A, B, C, D, & E with postgres installed with streaming replication as follow: A à B ( sync replication ) A à C ( async replication ) A

Re: How to choose new master from slaves.?

On 8/6/22 03:40, Peter J. Holzer wrote: On 2022-08-04 07:43:28 +, Sacheen Birhade wrote: I have a very basic question about streaming replication feature of Postgres. Let’s assume I have servers A, B, C, D, & E with postgres installed with streaming replication as follow: A à B ( sync repl

Re: How to choose new master from slaves.?

On 8/6/22 15:45, Peter J. Holzer wrote: On 2022-08-06 15:06:06 -0500, Ron wrote: On 8/6/22 03:40, Peter J. Holzer wrote: Using sync replication on an unstable link is probably not a good idea. Every time the link goes down, A freezes. Is this what you want? I had to fight my end users about

Re: Question about locking

On 8/7/22 00:30, Frank Millman wrote: Hi all Apologies if this is an FAQ, but I could not find the answer I was looking for. I want to run two SELECTs, one immediately after the other. I want to ensure that no other process updates the database in between the two. What is the best way to

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

On 8/9/22 09:39, Rob Sargent wrote: On Aug 9, 2022, at 7:04 AM, Taylor Smith wrote: Hi all, I have a database that is in excess of 20TB in size, partitioned by date on a month to month basis. There is a column within that stores text (validated to be json but not stored as JSONB). I have

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