Re: TEXT column > 1Gb

/Must/ the genome all be in one big file, or can you store them one line per table row? On 4/12/23 12:19, Joe Carlson wrote: I’ve certainly thought about using a different representation. A factor of 2x would be good, for a while anyway. For nucleotide sequence, we’d need to consider a 10 cha

Re: TEXT column > 1Gb

On 4/12/23 14:21, Rob Sargent wrote: On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? Not sure what OP is doing with plant genomes (other than some genomics) but the tools all use files and pipeline of sub-tools. In and out

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

On 4/13/23 09:44, Sebastien Flaesch wrote: Hello, Is there an easy way to convert JSON data containing ASP.NET AJAX Dates into PostgreSQL timestamp? I have this kind of JSON data: {     "PurchaseOrder" : "45",     "CreationDate" : "\/Date(167235840)\/",     "LastChangeDateTime" :

Re: Suppress logging of "pg_hba.conf rejects connection for host"

On 4/16/23 10:02, ertan.kucuko...@1nar.com.tr wrote: Hello, One of the systems running PostgreSQL 14.7 receive a lot of lines like in the subject. I have below pg_hba.conf line and that line causes these to be logged. host all all 0.0.0.0/0 reject If possible, I do not want to see these lines

pg_restore to new database wants to wipe out the old database?

Hi, v9.6.9 Why is pg_restore trying to drop my production database, when I (think I) am telling it to create the new database "Molson"? $ cd /backup $ pg_dump -d proddb -j 8 -Fd --no-synchronized-snapshots -Z0 -v -f proddb 2> proddb_pgdump.log $ mv proddb Molson $ pg_restore -vcC --if-exis

Re: pg_restore to new database wants to wipe out the old database?

On 10/30/2018 05:16 PM, David G. Johnston wrote: On Tue, Oct 30, 2018 at 3:09 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: Hi, v9.6.9 Why is pg_restore trying to drop my production database, when I (think I) am telling it to create the new database "Molson&qu

Truncated queries in the pg_log file

Hi, v8.4  (we're migrating to 9.6 tomorrow night, but the work must still proceed today.) SQL statements are being logged (which is good), but only the fist 400 or so bytes is recorded.  What knob do I tweak to record the whole query? Here are the relevant postgres.conf items which are not

Re: Truncation of UNLOGGED tables upon restart.

On 11/01/2018 03:58 PM, David G. Johnston wrote: On Thu, Nov 1, 2018 at 1:49 PM Ravi Krishna > wrote: Per documentation unlogged tables are not crash safe and PG will truncate it when it restarts after a crash. Does this apply to even read only unlogged tab

Re: Trouble Upgrading Postgres

On 11/03/2018 02:57 PM, Charles Martin wrote: I'd be grateful for some help. I am trying to move a large database from PostgreSQL 9.6 on Centos 6 to a different server using PostgreSQL 11 on Centos 7. I can't do a pg_dump because it always fails on the largest table. What error message? -- A

Re: backend crash on DELETE, reproducible locally

On 11/03/2018 02:19 PM, obo...@email.cz wrote: Hello, we reached the exactly same problem after upgrading to PostgreSQL 11 - the server crashed on a DELETE statement with a trigger.We also observed an AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem seems to be solved

Re: Trouble Upgrading Postgres

Not enough swap space? On 11/04/2018 04:55 PM, Charles Martin wrote: Yep, you called it: Nov  2 20:30:45 localhost kernel: Out of memory: Kill process 30438 (postmaster) score 709 or sacrifice child Nov  2 20:30:45 localhost kernel: Killed process 30438, UID 26, (postmaster) total-vm:3068900k

Re: Trouble Upgrading Postgres

On 11/05/2018 10:50 AM, Charles Martin wrote: [snip] The results I pasted were from: SELECT * FROM pg_settings Maybe I didn't get it the right way. We were expecting the output of the "SHOW SHARED_BUFFERS;" command. The system has only 4GB of RAM. I read that a reasonable swap size is 1/4

Re: Trouble Upgrading Postgres

On 11/05/2018 11:06 AM, Charles Martin wrote: Ron said: >We were expecting the output of the "SHOW SHARED_BUFFERS;" command. Ok, the result from that command is: 1GB >The system has only 4GB of RAM. I read that a reasonable swap size is 1/4 of RAM, so I've created a swap

CREATE OR REPLACE FUNCTION statement just sitting there

I've got a very puzzling problem on 9.6.6 systems we just migrated from 8.4.  (The same problem happened on 9.6.9, but rolled it back so as to make prod have the same version as our Staging systems.) We've got a giant script full of DROP TRIGGER IF EXISTS and CREATE TABLE and DROP TABLE and CR

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/05/2018 08:30 PM, Rob Sargent wrote: On 11/5/18 7:05 PM, Ron wrote: I've got a very puzzling problem on 9.6.6 systems we just migrated from 8.4.  (The same problem happened on 9.6.9, but rolled it back so as to make prod have the same version as our Staging systems.) We

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/06/2018 05:05 AM, Laurenz Albe wrote: Ron wrote: However, one or more of our big (and schema-identical) prod databases (which are each on a different server) it is finicky and tends to just "sit" at a random one of the CREATE OR REPLACE FUNCTION statements. The "li

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/06/2018 05:34 AM, Alvaro Herrera wrote: On 2018-Nov-05, Ron wrote: That (plus pg_locks)  is the heart of the "list all blocking queries" statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring. On that page there's a note about 9.6. Did you see the re

Re: PgAgent on Windows

On 11/06/2018 07:36 AM, Marcio Meneguzzi wrote: Hello, I´m trying use pgAgent on windows 10 and Windows Server 2012 R2. My version of PostgreSQL is 9.5.1.14 Install and configure pgAgent with sucess, but, when I try run a Job, status is Failed with a message bellow: *"Couldn't create the primar

Re: why select count(*) consumes wal logs

On 11/06/2018 11:12 AM, Michael Nolan wrote: On Tue, Nov 6, 2018 at 11:08 AM Ravi Krishna > wrote: PG 10.5 I loaded 133 million rows to a wide table (more than 100 cols) via COPY. It's always a good idea after doing a large scale data load to do a vacuum an

Re: Fwd: Log file

On 11/06/2018 12:06 PM, Igor Korot wrote: [snip] Ok. I guess I will have to write such function. Cron and the relevant log_* config variables should solve your problems. -- Angular momentum makes the world go 'round.

Idle query that's not ""?

Hi, v9.6.6 Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text ""? postgres=# select pid,    xact_start as txn_start,    to_char(EXTRACT(epoch FROM now() - query_start), '999,999.') as query_age_secs,

Re: Idle query that's not ""?

T-ACTIVITY-VIEW "Text of this backend's most recent query. If |state| is |active| this field shows the currently executing query. In all other states, it shows the last query that was executed. " El mar., 6 de nov. de 2018 a la(s) 15:46, Ron (ronljohnso...@gmail.com <ma

Re: Idle query that's not ""?

On 11/06/2018 03:04 PM, David G. Johnston wrote: On Tue, Nov 6, 2018 at 1:59 PM Ron wrote: Right. But when does the query text become ""? Or has that become obsolete? (We recently migrated from 8.4.) That behavior changed sometime around 9.0; since it always shows the last quer

Re: Running pg_upgrade Version 11

On 11/06/2018 06:30 PM, rob stone wrote: On Tue, 2018-11-06 at 15:17 +0900, Michael Paquier wrote: On Tue, Nov 06, 2018 at 04:27:35PM +1100, rob stone wrote: Logged in as user postgres and postgres owns the files created by initdb, so is this a permissions problem or am I having a brain fade?

REINDEX CONCURRENT?

Hi, Is this very useful feature on the TODO list, and high enough that it might get into v12?  (Indexes supporting PK and FK constraints just can't be dropped to use CREATE INDEX CONCURRENT.) Thanks -- Angular momentum makes the world go 'round.

Re: pg_dump out of memory for large table with LOB

On 11/09/2018 05:49 PM, Jean-Marc Lessard wrote: I am running PostgreSQL 9.6.5 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit on win2012 with 12Gb RAM The dumped table is 0.5TB, 17 million rows and LOB uses about 99% of the space. The pg_dump consum

Re: Move cluster to new host, upgraded version

On 11/11/2018 02:51 PM, Rich Shepard wrote: On Sun, 11 Nov 2018, Adrian Klaver wrote: pg_dumpall is going to need to run against a Postgres server not just a data directory. Adrian,   Of course. Yet it's the data directory that's written to the .sql file. Unless your db is small, do a para

Re: Move cluster to new host, upgraded version

On 11/12/2018 07:55 AM, Rich Shepard wrote: On Sun, 11 Nov 2018, Ron wrote: Unless your db is small, do a parallel dump. Even then, do a "-Fc" backup instead. That's been the recommended method for many years. Ron,   I've several databases, none 'large.' Wh

Re: WTF with hash index?

On 11/13/2018 12:07 PM, Andreas Kretschmer wrote: Am 13.11.2018 um 17:42 schrieb Олег Самойлов: insert into gender (gender) select case when random<0.50 then 'female' when random<0.99 then 'male' else 'other' end from (select random() as random, generate_series(1,:table_size)) as subselect;

Re: WTF with hash index?

On 11/13/2018 12:39 PM, Andreas Kretschmer wrote: Am 13.11.2018 um 19:12 schrieb Ron: On 11/13/2018 12:07 PM, Andreas Kretschmer wrote: Am 13.11.2018 um 17:42 schrieb Олег Самойлов: insert into gender (gender) select case when random<0.50 then 'female' when random<0.99

Re: Default Privilege Table ANY ROLE

On 11/14/2018 08:19 AM, Nicolas Paris wrote: Hi I d'like my user be able to select on any new table from other users. ALTER DEFAULT PRIVILEGES FOR ROLE "theowner1" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" ALTER DEFAULT PRIVILEGES FOR ROLE "theowner2" IN SCHEMA "myschema"

Re: pg_dump out of memory for large table with LOB

On 11/14/2018 11:14 AM, Jean-Marc Lessard wrote: Adrien Nayrat wrote: > With 17 million LO, it could eat lot of memory ;) Yes it does. I did several tests and here are my observations. First memory settings are: shared_buffers = 3GB work_mem = 32Mb maintenance_work_mem = 1GB effective_cache_siz

The age() of a column?

What does it mean, and where is it documented?  The only place in the docs I see reference to age() is https://www.postgresql.org/docs/9.6/functions-datetime.html and this doesn't seem to fit that use case. Thanks TAPb=# select COALESCE(ta.schemaname, 'pg_toast') || '.' || cl.relname,   

Re: psql is hanging

On 11/30/2018 09:53 AM, John Smith wrote: We have a long script of sql that we run, several thousand lines of sql. If I execute the script from start to finish, somewhere in the middle of it, one sql command will hang and take 2 to 3 hours. During this time, "htop" shows 100% cpu usage with a p

Re: psql is hanging

On 11/30/2018 10:38 AM, Tom Lane wrote: Ron writes: We have a seemingly identical problem in v9.6 on our big prod databases.  It worked perfectly in 8.4 *and works perfectly in 9.6 our small staging servers*. A giant sql script that drops the oldest child tables from a large set of partitioned

Unused indexes

For indexes that existed before the cluster was last started, and for which REINDEX hasn't been run on them since the cluster was last started... is it valid to say that an index has not been used since the cluster was started if these three pg_stat_all_indexes fields all have a value of 0? id

Re: Transition Tables doesn´t have OID

On 12/01/2018 06:22 AM, PegoraroF10 wrote: I´m trying to use transition tables for auditing purposes. create trigger MyTableAudit_UPD after update on MyTable referencing old table as Transition_old new table as Transition_new for each statement execute procedure AuditTable(); create or replace

Re: simple division

Use CAST() instead of ::. SELECT CAST(alldefects AS NUMEREIC(10,4))/wordcount; On 12/04/2018 02:57 PM, Martin Mueller wrote: I didn't formulate my question properly, because the query went like "select alldefects /wordcount" where alldefects and wordcount are integers. But none of the diffe

Re: psql is hanging

On 11/30/2018 09:53 AM, John Smith wrote: We have a long script of sql that we run, several thousand lines of sql. If I execute the script from start to finish, somewhere in the middle of it, one sql command will hang and take 2 to 3 hours. During this time, "htop" shows 100% cpu usage with a p

Re: Moving large table between servers: logical replication or postgres_fdw

On 12/05/2018 12:05 AM, Rob Sargent wrote: On Dec 4, 2018, at 8:14 PM, Rhys A.D. Stewart wrote: Greetings Folks, I have a relatively large table (100m rows) that I want to move to a new box with more resources. The table isn't doing anything...i.e its not being updated or read from. Which appr

Re: Moving large table between servers: logical replication or postgres_fdw

On 12/04/2018 09:14 PM, Rhys A.D. Stewart wrote: Greetings Folks, I have a relatively large table (100m rows) that I want to move to a new box with more resources. The table isn't doing anything...i.e its not being updated or read from. Which approach would be faster to move the data over: a

Re: Moving large table between servers: logical replication or postgres_fdw

pg_backup/pg_restore will work, but parallel is pointless on a single (unpartitioned) table. On 12/05/2018 12:13 AM, Rene Romero Benavides wrote: I tend to believe that a backup (pg_dump) in custom format (-F c) using multiple jobs (parallel) -> restore (pg_restore) also with multiple concurre

Re: surprising query optimisation

On 12/05/2018 08:42 AM, Chris Withers wrote: On 05/12/2018 14:38, Stephen Frost wrote: Greetings, * Chris Withers (ch...@withers.org) wrote: On 30/11/2018 15:33, Stephen Frost wrote: * Chris Withers (ch...@withers.org) wrote: On 28/11/2018 22:49, Stephen Frost wrote: For this, specifically,

Re: Limitting full join to one match

On 12/05/2018 06:34 PM, Phil Endecott wrote: Dear Experts, I have a couple of tables that I want to reconcile, finding rows that match and places where rows are missing from one table or the other: db=> select * from a; +++ |date| amount | +++ | 2

n_mod_since_analyze

https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW Does the field n_mod_since_analyze use "mod" instead of "upd" because it includes inserts, updates and deletes? Thanks -- Angular momentum makes the world go 'round.

Re: psql profiles?

On 12/07/2018 10:45 AM, Matt Zagrabelny wrote: On Fri, Dec 7, 2018 at 7:42 AM Arthur Zakirov > wrote: On 07.12.2018 01:34, Matt Zagrabelny wrote: > > > On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera mailto:alvhe...@2ndquadrant.com> >

Tables(s) that feed pg_controldata

In v9.2 (yes, I know it's EOL; there's nothing I can do about it), what tables do I query to get these values, and can I also get them from the streamed replication host? Database cluster state Latest checkpoint location Time of latest checkpoint [postgres@fpslbxhaprl05 ~]$ pg_controldata p

pg_ctl kill INT vs. pg_cancel_backend

Hi, Is there a substantive difference between the two, or are they just different interfaces to the same action? -- Angular momentum makes the world go 'round.

Re: Tables(s) that feed pg_controldata

On 12/09/2018 07:51 PM, Ian Barwick wrote: On 12/09/2018 01:25 AM, Ron wrote: > > In v9.2 (yes, I know it's EOL; there's nothing I can do about it), what tables > do I query to get these values, and can I also get them from the streamed > replication host? > &

Re: Tables(s) that feed pg_controldata

On 12/09/2018 08:50 PM, Michael Paquier wrote: [snip] (9.2 is EOL'd for one year now, you may want to upgrade.) You assume that the DBA controls the data; he doesn't.  It's the customer's data, and they control the OS, RDBMS and application versions.  We just migrated the big databases off an

What is the tuplestore?

Hi, v9.6.6 2018-12-07 06:21:55.504 EST 10.140.181.89(35868) CDS CDSLBXW 13748 SELECT PostgreSQL JDBC Driver 53100 ERROR:  could not write to tuplestore temporary file: No space left on device I see this in the pg_log file, but #1 can't figure out what "tuplestore" is (Google doesn't help),

Re: pg_dump: ERROR: array size exceeds the maximum allowed (268435455)

On 12/10/2018 03:15 PM, Laurent FAILLIE wrote: Hello, I'm trying to save a database from a dying disk on a Gentoo/Linux box. [snip] This box is running pg_dump (PostgreSQL) 10.3 but I was on way to upgrade to 10.6 when I discovered this issue. Since 10.3 and 10.6 are the same primary ve

Re: What is the tuplestore?

There's certainly a problem with the application, but the error is in the pg_log, not the application log. On 12/10/2018 03:21 PM, Rene Romero Benavides wrote: What if this error message pertains to something happening on the application side? Am Mo., 10. Dez. 2018 um 09:56 Uhr schrie

Re: What is the tuplestore?

neral/02ag7k8gcr/tuplestore-write-failed do you have space usage charts for that partition? doesn't it show a spike during that time? Am Mo., 10. Dez. 2018 um 15:54 Uhr schrieb Ron <mailto:ronljohnso...@gmail.com>>: There's certainly a problem with the application, but the er

Re: What is the tuplestore?

n you identify something that could have required lots of temporary space? Am Mo., 10. Dez. 2018 um 17:33 Uhr schrieb Ron <mailto:ronljohnso...@gmail.com>>: Which file system (specifically, which directory)?  Is it data/base/pgsql_tmp?  There's 96GB free, which is 74% of the vo

Re: What is the tuplestore?

On 12/10/2018 06:24 PM, Thomas Munro wrote: On Tue, Dec 11, 2018 at 2:56 AM Ron wrote: 2018-12-07 06:21:55.504 EST 10.140.181.89(35868) CDS CDSLBXW 13748 SELECT PostgreSQL JDBC Driver 53100 ERROR: could not write to tuplestore temporary file: No space left on device I see this in the pg_log

Re: explain analyze cost

On 12/12/2018 04:37 PM, Ravi Krishna wrote: I am running explain analyze cost on a SQL which reads from two large tables (122mil and 37 mil).  The query is an UPDATE SQL where we use derives table in the from clause and then join it back to the table being updated. The explain analyze cost it

shared_buffers on Big RAM systems

Hi, https://www.postgresql.org/docs/9.6/runtime-config-resource.html The docs say, "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25%". But that's pretty archaic in 2018.  What if the dedicated database server has 128GB RAM

Installing pg_buffercache (was Re: shared_buffers on Big RAM systems)

On 12/13/2018 08:25 PM, Rene Romero Benavides wrote: This topic seems to be always open to discussion. In my opinion, it depends on how big your work dataset is, there's no use in sizing shared_buffers beyond that size. I think, the most reasonable thing is analyzing each case as proposed here:

Re: simple query on why a merge join plan got selected

I'd run each query multiple times -- before and after disabling mergejoin -- to ensure that all the data is safely in RAM. On 12/15/2018 02:13 PM, Vijaykumar Jain wrote: Hey Guys, I was just playing with exploring joins and plans i came across this create table t1(a int); create table t2(a in

Re: conditionally terminate psql script

On 12/17/2018 09:01 AM, Jerry Sievers wrote: haman...@t-online.de writes: Hi, many thanks -- too bad I am still using 9.3 Just because your server backend is 9.3 does not rule out using much newer clients, such as psql. While technically true, and is useful, many production servers (especia

Re: Alter table column constraint

On 12/17/2018 02:20 PM, Rich Shepard wrote: On Mon, 17 Dec 2018, Melvin Davidson wrote: Yes, you must drop then add the revised constraint. However, from your statement above, it sounds to me as if you would be better off using A FOREIGN kEY CONSTRAINT. It makes things a lot simpler. Melvin,

Re: Format an Update with calculation

Bret, That's just an example of how to use Postgres' cast syntax.  You'd write: UPDATE im_ci_item_transfer    SET suggested_retail_price=(suggested_retail_price + (suggested_retail_price * .13))::numeric(7,2) WHERE item_code='0025881P2'; I prefer the round() function, though. On 12/18/2018 0

Re: Format an Update with calculation

On 12/19/2018 02:12 AM, Condor wrote: On 18-12-2018 15:51, Adrian Klaver wrote: [snip] In addition to what Pavel posted: select round(43.2335, 2);  round ---  43.23 Beware with round and numeric select round(43.2375, 2);  round ---  43.24  select 43.2375::numeric(17, 2);  numeri

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

On 12/19/18 7:27 PM, Michael Paquier wrote: [snip] Each backend stores its own copy of the relation cache, so if you have idle connections which have been used for other work in the past then the memory of those caches is still around. Idle connections also have a CPU cost in Postgres when build

Re: Watching for view changes

On 12/20/18 3:17 AM, Mitar wrote: Hi! I am trying to see how I could use NOTIFY/LISTEN to watch changes of a complicated SELECT query which spans multiple tables. Ideally, I would like to leave to PostgreSQL to determine when some data (and which data) in the result of the SELECT query has chang

Re: Watching for view changes

On 12/20/18 2:20 PM, Mitar wrote: Hi! On Thu, Dec 20, 2018 at 12:07 PM Rob Sargent wrote: Are you hoping to see the difference in the returned values for successive calls to the same query? i would like to in as close to real-time as possible get notification when results of a query would ha

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

But Jeff said "left open indefinitely without either committing or rolling back".  Your process is committing the transaction. On 1/2/19 6:15 AM, Mark wrote: Hi Jeff, Thanks for your help. That is exactly what is happening. I have a long running job which deletes all of the common_student tab

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

Try using SERIALIZABLE transactions instead of the default READ COMMITTED. On 1/2/19 9:28 AM, Mark wrote: Hi Ron, Yes, my process will commit the transaction (or roll it back) eventually. It's the window where one transaction has deleted all the rows (before committing) and an ANALYS

Re: Implementing standard SQL's DOMAIN constraint [RESOLVED]

On 1/2/19 12:05 PM, Rich Shepard wrote: On Wed, 2 Jan 2019, David G. Johnston wrote: You add the create domain command once before any objects that make use of it. David,   This is the answer I sought: postgres supports the create domain command. I did not see this in your first response.

Re: Dropping and creating a trigger

On 1/5/19 3:59 AM, Mitar wrote: Hi! I am seeing such errors in logs: ERROR: trigger "myapp_assignments" for relation "assignments" already exists STATEMENT: BEGIN TRANSACTION; DROP TRIGGER IF EXISTS "myapp_assignments" ON "assignments"; CREATE TRIGGER "myapp_assignments" AFTER INSERT OR UPDATE

Re: Is it impolite to dump a message

On 1/6/19 9:33 AM, Stephen Frost wrote: Greetings, * Thiemo Kellner (thi...@gelassene-pferde.biz) wrote: I posted a question and did not get a reaction. Now I wonder whether no one took interest (no offence meant) no one has an answer or whether the point in time I posted was just rubbish. In t

Re: About SSL connection

On 1/6/19 8:10 AM, John Mikel wrote: Hi, i was trying to enable SSL on postgresql 10 and 11 Servers after searching on postregsql documentation i found this : ALTER SYSTEM SET/|configuration_parameter|/ { TO | = } {/|value|/ | '/|value|/' | DEFAULT } on https://www.postgresql.org/docs/10/sql-

Re: Which queries have run query trace form .exe.

On 1/7/19 6:52 AM, Vimal Kanzariya wrote: *Hello,* I need find out some database operation which is executed form One of the .exe. I have installed that .exe into my system and once i executed that .exe it is executed some operation into PostgreSQL 9.5 (version 1.22.1). So i want to know what

Re: Pulling data from Postgres DB table for every 5 seconds.

On 1/9/19 11:02 AM, github kran wrote: Hi Postgres Team, I have an application using RDS Aurora Postgresql 9.6 version having 4 TB of DB size. In this DB we have a table PRODUCT_INFO with around  1 million rows and table size of 1 GB. We are looking for a implementation whe

Re: postgres operational

On 1/9/19 12:19 PM, Martín Marqués wrote: El 9/1/19 a las 14:58, Steve Clark escribió: Hi List, Is there a sure fire way to tell if postgres server is up an operational. I was testing to see if the socket at /tmp/.s.PGSQL.5432 existed - but I ran into a recent problem on CentOS 7.5, postgresq

Re: Refining query statement

On 1/15/19 9:47 AM, Thomas Kellerer wrote: Rich Shepard schrieb am 15.01.2019 um 16:39:   Working with my sales/client management system using psql I have a select statement to identify contacts to be made. This statement works: select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_nam

Re: Read consistency when using synchronous_commit=off

A crash-unsafe database is for data you don't care about. On 1/16/19 2:27 AM, pshadangi wrote: Thanks Ravi for the clarification, we will go ahead with "synchronous_commit=off". On Wed, Jan 16, 2019 at 10:47 AM Ravi Krishna > wrote: Sorry I misunderstood.

Re: Capacity Planning

IOW, the same as every other RDBMS... On 1/20/19 1:29 AM, Allan Kamau wrote: Some of the aspects you may want to consider are: 1) Per unit time (day or week), how much data will be persisted to the database, number of records and total size of the data. 2) How much of these data will be updated

Re: Tuning threshold for BAS_BULKREAD (large tables)

On 1/22/19 1:35 AM, Jamison, Kirk wrote: Hi, I have a source code-related question on BufferAccessStrategyType BAS_BULKREAD. Currently, this access method is set internally to cache tables larger than 1/4 of shared_buffers. src/backend/access/heap/heapam.c:initscan() if (!RelationUs

Re: Memory and hard ware calculation :

Have you analyzed the queries to ensure that they are efficient? Have you examined the tables to ensure that they have indexes to support the joins? Have you minimized the amount of data selected? On 1/22/19 6:54 AM, Rangaraj G wrote: Hi, My question Our connection is 1100 parallel connect

Re: how to properly start postgresql with no TCP listeners in ubuntu 16.04 LTS

On 1/23/19 8:41 PM, Alex Morris wrote: On 1/23/19 19:15, Stephen Frost wrote: Greetings, * Alex Morris (alex.mor...@twelvemountain.com) wrote: This question may simply be my ignorance of what piece of the systemd / systemctl puzzle needs attention.  Any clues are appreciated. The simplest ap

Re: Need a command to take the backup of the child tables along with its master table.

On 1/24/19 3:40 AM, Raghavendra Rao J S V wrote: Hi All, We have one master table and multiple child tables (dependent tables)  associated with it. While taking the backup of the master table , I would like to take the backup of all its child (dependent) tables backup also. Please guide me

[pgbackrest] Expiring the last backup?

Hi, The backups partition is running out of disk space, and I need to delete the only backup. $ pgbackrest expire --stanza=localhost --retention-full=0 ERROR: [032]: '0' is out of range for 'repo1-retention-full' option So, in version 2.07, what's the secret sauce for deleting that final copy

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 7:26 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: The backups partition is running out of disk space, and I need to delete the only backup. $ pgbackrest expire --stanza=localhost --retention-full=0 ERROR: [032]: '0' is out of range for '

Re: Casting Integer to Boolean in assignment

On 1/24/19 5:04 AM, Alexandre GRAIL wrote: Hello the list, Maybe this question has been debated before (I didn't find anything helpful) but : Why the default is to throw an error when casting Integer to Boolean in assignment, and accepting it everywhere else ? So you can type : postgres

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 8:11 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: On 1/24/19 7:26 AM, Stephen Frost wrote: * Ron (ronljohnso...@gmail.com) wrote: The backups partition is running out of disk space, and I need to delete the only backup. $ pgbackrest expire --stanza

Re: Casting Integer to Boolean in assignment

On 1/24/19 9:05 AM, Geoff Winkless wrote: On Thu, 24 Jan 2019 at 14:28, David G. Johnston wrote: To assist developers in avoiding the writing of buggy queries. Amazing how many of these developers find this a hindrance. If only they could see how helpful we're being to them. It's the C vs. A

Re: Recommended Hardware requirements for PostgreSQL DB Server.

On 1/24/19 12:06 PM, Kaushal Shriyan wrote: Hi, Are there any recommended hardware requirements to setup PostgreSQL DB server?  I checked https://wiki.postgresql.org/wiki/Database_Hardware but unable to find it. I am going to install Postgresql 9.6 version in AWS (https://aws.amazon.com/ec2/i

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 11:22 PM, David Steele wrote: On 1/24/19 5:07 PM, Ron wrote: Are these the steps, or am I missing something? $ pgbackrest stop $ pgbackrest stanza-delete --stanza=mystanza--force That looks right but no need for --force.  That's what the `stop` is for -- to let pgBackRest

Re: Query help

On 1/26/19 5:04 PM, Chuck Martin wrote: I'm having trouble formulating a query. This is a simplified version of the tables: ombcase case_pkey integer, primary key casename varchar insdatetime timestamp w/o time zone status_fkey integer, foreign key status status_pkey inte

Re: Regarding query execution for long time

On 1/28/19 6:20 AM, Durgamahesh Manne wrote: Hi below query is being executed for long time Select distinct ltrim(rtrim(ssnumber)), CL.iInsightClientId, ltrim(rtrim(TFA.client_account_key)) as vchAccountNumber, concat(TFA.first_name, ' ', TFA.last_name) as vchAccountName, concat('AP:TIAA', TF

Re: Regarding query execution for long time

On 1/28/19 8:10 AM, Durgamahesh Manne wrote: On Mon, Jan 28, 2019 at 6:34 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: On 1/28/19 6:20 AM, Durgamahesh Manne wrote: > Hi > > below query is being executed for long time > > Select > di

Re: Querying w/ join slow for large/many child tables

On 1/29/19 8:49 PM, Wells Oliver wrote: I have a primary parent table with a child table per week of the year for each week back through 2015. There are a lot of child tables. Each week's child table has maybe 80-110m rows. A *partititioned* table? When I join to the parent table on a colum

Re: pg_rewind success even though getting error 'record with incorrect prev-link'

On 1/29/19 9:57 PM, Abdullah Al Maruf wrote: Hi Michael > This is pointing out to the end of WAL for the current timeline.  You > may face it after reading a WAL segment in an area which has been used > in the past for a recycled segment. Are you talking about error `LOG:  invalid record length

Re: pgexpress 4.60 vita voom

On 1/30/19 5:22 AM, ceuro wrote: Hello everybody, this is my first post. I need to update to vita voom pgexpress 4.60 in order to upgrade to postgresql 9.2: I've tried to contact vita voom to collect (I've also already payed) the last release of the driver but nobody answer me: Is there anybody t

Re: problem

On 1/31/19 7:10 AM, Mirco Gallazzi wrote: GoodMornig, I can’t install postgres on my pc because i have Always this error: “ Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.” I looked for a guide that can help me to solve

Date calculation

Hi, v9.6.6 Is there a built in function to calculate, for example, next Sunday? For example, postgres=# select current_date, next_dow(current_date, 'Sunday');     date    |    date |  2019-01-31 | 2019-02-03 (1 row) Thanks -- Angular momentum makes the world go 'round

Re: Date calculation

On 1/31/19 2:15 PM, Bruce Momjian wrote: On Thu, Jan 31, 2019 at 02:11:14PM -0600, Ron wrote: Hi, v9.6.6 Is there a built in function to calculate, for example, next Sunday? For example, postgres=# select current_date, next_dow(current_date, 'Sunday');     date   

Re: Copy entire schema A to a different schema B

On 2/11/19 10:00 AM, Tiffany Thang wrote: Hi, To copy the source schema A to target schema B in the same database in PG10.3, I use psql to dump schema A and manually removes anything specific to the schema in the text dump file before importing into schema B. How do I achieve the same exportin

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