Re: PG12 autovac issues

On Mon, Mar 23, 2020 at 10:40:39PM -0700, Andres Freund wrote: > On 2020-03-24 14:26:06 +0900, Michael Paquier wrote: >> Nothing really fancy: >> - autovacuum_vacuum_cost_delay to 2ms (default of v12, but we used it >> in v11 as well). >> - autovacuum_naptime = 15s >> - autovacuum_max_workers = 6 >

Re: PG12 autovac issues

Hi, On 2020-03-24 14:26:06 +0900, Michael Paquier wrote: > > Could you share what the config of the server was? > > Nothing really fancy: > - autovacuum_vacuum_cost_delay to 2ms (default of v12, but we used it > in v11 as well). > - autovacuum_naptime = 15s > - autovacuum_max_workers = 6 > - log_

Re: PG12 autovac issues

On Mon, Mar 23, 2020 at 01:00:51PM -0700, Andres Freund wrote: > On 2020-03-23 20:47:25 +0100, Julien Rouhaud wrote: >>> - relfrozenxid, age(relfrozenxid) for the oldest table in the oldest >>> database >>> SELECT oid::regclass, age(relfrozenxid), relfrozenxid FROM pg_class WHERE >>> relfrozenx

Re: Loading 500m json files to database

On Mon, 2020-03-23 at 03:24 -0700, pinker wrote: > [EXTERNAL SOURCE] > > > > Hi, do you have maybe idea how to make loading process faster? > > I have 500 millions of json files (1 json per file) that I need to load to > db. > My test set is "only" 1 million files. > > What I came up with now

Partitioned table migration strategy

Hello all, Are there any migration strategies/best practices when migrating from PG11 partitioned tables to PG12 partitioned tables that don't involve extended downtime? We have about 900 partitions (hash partitions) in PG11 with millions of rows that we need to migrate to PG12 and (ideally) would

Re: Loading 500m json files to database

hmm now I'm thinking maybe setting up pgbouncer in front of postgres with statement mode would help? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Loading 500m json files to database

it's in a blob storage in Azure. I'm testing with 1m that I have locally -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Loading 500m json files to database

> On Mar 23, 2020, at 7:11 PM, David G. Johnston > wrote: > > On Mon, Mar 23, 2020 at 3:24 AM pinker > wrote: > time for i in datafiles/*; do > psql -c "\copy json_parts(json_data) FROM $i"& > done > > Don't know whether this is faster but it does avoid spinning up a

Re: Loading 500m json files to database

On Mon, Mar 23, 2020 at 3:24 AM pinker wrote: > time for i in datafiles/*; do > psql -c "\copy json_parts(json_data) FROM $i"& > done > Don't know whether this is faster but it does avoid spinning up a connection multiple times. #bash, linux function append_each_split_file_to_etl_load_scr

Re: Loading 500m json files to database

On 3/23/20 5:23 PM, pinker wrote: it's a cloud and no plpythonu extension avaiable unfortunately I presume Python itself is available, so would it not be possible to create a program that concatenates the files into batches and COPY(s) that data into Postgres using the Psycopg2 COPY functions

Re: Loading 500m json files to database

On 3/23/20 5:26 PM, pinker wrote: Hi, json_parts it's just single table with 2 column: Well I misread that. Table "public.json_parts" Column | Type | Collation | Nullable |Default | Storage | Stats target | Descripti

Re: Loading 500m json files to database

Hi, json_parts it's just single table with 2 column: Table "public.json_parts" Column | Type | Collation | Nullable |Default | Storage | Stats target | Description ---+-+---+

Re: Loading 500m json files to database

there is no indexes nor foreign keys, or any other constraints -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Loading 500m json files to database

it's a cloud and no plpythonu extension avaiable unfortunately -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Loading 500m json files to database

Ertan Küçükoğlu wrote > However, if possible, you may think of using a local physical computer to > do all uploading and after do backup/restore on cloud system. > > Compressed backup will be far less internet traffic compared to direct > data inserts. I was thinking about that but data source is

Re: Loading 500m json files to database

Christopher Browne-3 wrote > Well, you're paying for a lot of overhead in that, as you're > establishing a psql command, connecting to a database, spawning a backend > process, starting a transactions, committing a transaction, closing the > backend > process, disconnecting from the database, and c

Re: PG12 autovac issues

On Mon, Mar 23, 2020 at 4:31 PM Justin King wrote: > > On Mon, Mar 23, 2020 at 3:00 PM Andres Freund wrote: > > > > Hi, > > > > On 2020-03-23 20:47:25 +0100, Julien Rouhaud wrote: > > > > - relfrozenxid, age(relfrozenxid) for the oldest table in the oldest > > > > database > > > > SELECT oid::

Re: PG12 autovac issues

Hi, On 2020-03-23 16:31:21 -0500, Justin King wrote: > This is occurring in our environment right now (started about 30 min > ago). Here 's the latest logs (grepped by vacuum): > > Mar 23 20:54:16 cowtn postgres[15569]: [12-1] 2020-03-23 20:54:16.542 > GMT [15569] LOG: automatic vacuum of table

Re: PG12 autovac issues

On Mon, Mar 23, 2020 at 3:00 PM Andres Freund wrote: > > Hi, > > On 2020-03-23 20:47:25 +0100, Julien Rouhaud wrote: > > > - relfrozenxid, age(relfrozenxid) for the oldest table in the oldest > > > database > > > SELECT oid::regclass, age(relfrozenxid), relfrozenxid FROM pg_class > > > WHERE r

Re: PG12 autovac issues

Hi, On 2020-03-23 20:47:25 +0100, Julien Rouhaud wrote: > > - relfrozenxid, age(relfrozenxid) for the oldest table in the oldest > > database > > SELECT oid::regclass, age(relfrozenxid), relfrozenxid FROM pg_class WHERE > > relfrozenxid <> 0 ORDER BY age(relfrozenxid) DESC LIMIT 1; > > The vm

Re: PG12 autovac issues

Hi, On Mon, Mar 23, 2020 at 09:23:03AM -0700, Andres Freund wrote: > Hi, > > On 2020-03-23 16:22:47 +0100, Julien Rouhaud wrote: > > On Fri, Mar 20, 2020 at 12:03:17PM -0700, Andres Freund wrote: > > > Hi, > > > > > > On 2020-03-20 12:42:31 -0500, Justin King wrote: > > > > When we get into this

Re: Passwordcheck configuration

Greetings, * Dave Hughes (dhughe...@gmail.com) wrote: > Thank you for the information! This issue originated from a Department of > Defense STIG (Security Technical Implementation Guides). It's a security > check that applications and databases have to go through. I'll just leave > this one as

Re: Passwordcheck configuration

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Dave Hughes writes: > > I have a requirement to set some password complexity for our database such > > as length of password, upper case, lower case, special characters, > > expiration limit, reuse, etc. > > Usually, if you have to do something

Re: How does pg_basebackup manage to create a snapshot of the filesystem?

Greetings, * Dennis Jacobfeuerborn (denni...@conversis.de) wrote: > I'm currently trying to understand how backups work. In the > documentation in section "25.2. File System Level Backup" it says that > filesystem level backups can only be made when the database if offline > yet pg_basebackup seem

Re: Could postgres12 support millions of sequences? (like 10 million)

On Mon, Mar 23, 2020 at 9:58 AM Daniel Verite wrote: > pabloa98 wrote: > > > When I have a medium number of sequence I will report how it behaves. It > > will take some time though. > > Be aware that creating the sequences on the fly has the kind of race > condition that you wanted to avo

Re: Could postgres12 support millions of sequences? (like 10 million)

pabloa98 wrote: > When I have a medium number of sequence I will report how it behaves. It > will take some time though. Be aware that creating the sequences on the fly has the kind of race condition that you wanted to avoid in the first place. For instance consider this execution in two

Re: Runtime partition pruning

Thanks. Yes, the query with the same parameter seems to work as expected. It doesn't help us though as we are trying to transparently support partitioning using an ORM and we cannot change the parameters. Using the column name would have been much easier. Regards, Radu On Mon, Mar 23, 2020 at 5:5

Re: Is it safe to rename an index through pg_class update?

Just in case somebody else also needs such a functionality in PostgreSQL < 12, I made a function in plpgsql: https://github.com/kouber/pg_utils/blob/master/rename_index.sql -- Kouber Saparev

Re: PG12 autovac issues

Hi, On 2020-03-23 16:22:47 +0100, Julien Rouhaud wrote: > On Fri, Mar 20, 2020 at 12:03:17PM -0700, Andres Freund wrote: > > Hi, > > > > On 2020-03-20 12:42:31 -0500, Justin King wrote: > > > When we get into this state again, is there some other information > > > (other than what is in pg_stat_s

Re: Runtime partition pruning

> > select * from test where id between client_id-10 and client_id+10 and > client_id=?; > > does not (it scans all partitions in parallel) . > Is it expected? > Yes. But the below would work fine I expect since the planner would know a constant range for id. I would be very surprised if the opti

Re: Loading 500m json files to database

On 3/23/20 3:24 AM, pinker wrote: Hi, do you have maybe idea how to make loading process faster? I have 500 millions of json files (1 json per file) that I need to load to db. My test set is "only" 1 million files. What I came up with now is: time for i in datafiles/*; do psql -c "\copy jso

Re: PG12 autovac issues

On Fri, Mar 20, 2020 at 12:03:17PM -0700, Andres Freund wrote: > Hi, > > On 2020-03-20 12:42:31 -0500, Justin King wrote: > > When we get into this state again, is there some other information > > (other than what is in pg_stat_statement or pg_stat_activity) that > > would be useful for folks here

Re: Loading 500m json files to database

On 3/23/20 4:24 AM, pinker wrote: Hi, do you have maybe idea how to make loading process faster? I have 500 millions of json files (1 json per file) that I need to load to db. My test set is "only" 1 million files. What I came up with now is: time for i in datafiles/*; do psql -c "\copy

Runtime partition pruning

Hello list, Is runtime partition pruning available in PostgreSQL 12? I have a table partitioned by range on column id (primary key). For the query (client_id is passed as a parameter from the application): select * from test where id between>0 and and id<1000 and client_id=? ; partition prun

Re: Loading 500m json files to database

On Mon, 23 Mar 2020 at 06:24, pinker wrote: > Hi, do you have maybe idea how to make loading process faster? > > I have 500 millions of json files (1 json per file) that I need to load to > db. > My test set is "only" 1 million files. > > What I came up with now is: > > time for i in datafiles/*;

Re: Passwordcheck configuration

On Fri, 2020-03-20 at 12:30 -0400, Dave Hughes wrote: > Thank you for the information! This issue originated from a Department of > Defense STIG > (Security Technical Implementation Guides). It's a security check that > applications > and databases have to go through. I'll just leave this one

Re: Loading 500m json files to database

> On Mar 23, 2020, at 5:59 AM, Andrei Zhidenkov > wrote: > > Try to write a stored procedure (probably pl/python) that will accept an > array of JSON objects so it will be possible to load data in chunks (by > 100-1000 files) which should be faster. > >>> On 23. Mar 2020, at 12:49, Ertan

Re: Loading 500m json files to database

Try to write a stored procedure (probably pl/python) that will accept an array of JSON objects so it will be possible to load data in chunks (by 100-1000 files) which should be faster. > On 23. Mar 2020, at 12:49, Ertan Küçükoğlu > wrote: > > >> On 23 Mar 2020, at 13:20, pinker wrote: >> >

Re: Loading 500m json files to database

> On 23 Mar 2020, at 13:20, pinker wrote: > > Hi, do you have maybe idea how to make loading process faster? > > I have 500 millions of json files (1 json per file) that I need to load to > db. > My test set is "only" 1 million files. > > What I came up with now is: > > time for i in datafi

Re: Postgres cluster setup

> > Do you have similar setup like Oracle RAC in postgres core . I found in edb > but didn't find anything in postgres core. We are looking for setting up > replication with no outage and other node will be up , if the primary is > down. Any help would be great 😊 Oracle RAC is based on shared

Loading 500m json files to database

Hi, do you have maybe idea how to make loading process faster? I have 500 millions of json files (1 json per file) that I need to load to db. My test set is "only" 1 million files. What I came up with now is: time for i in datafiles/*; do psql -c "\copy json_parts(json_data) FROM $i"& done wh