Re: Must re-connect to see tables

If it worked in 9.3.22 and now it's failed in 9.3.22, then  I'd look to see if something has changed in data.sql. On 03/27/2018 06:22 AM, Blake McBride wrote: Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works.  What id does (e

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

On 03/28/2018 03:05 AM, Andreas Kretschmer wrote: [snip] > This e-mail message, including any attachments, this is a public mailing list ... The intended recipient is the public mailing list, no? -- Angular momentum makes the world go 'round.

Re: Postgresql Split Brain: Which one is latest

You need to find out when the split happened, and whether each new master have records since then. On 04/10/2018 11:47 AM, Vikas Sharma wrote: Thanks Adrian and Edison, I also think so. At the moment I have 2 masters, as soon as slave is promoted to master it starts its own timeline and applic

Re: pg_basebackup restore a single table

On 04/11/2018 10:21 AM, Andreas Kretschmer wrote: Am 11.04.2018 um 15:53 schrieb camarillo: Can I do a restore of a single table or single base using the archive generated for the basebackup without having to delete the filesystem (/var/lib/pgsql/9.5/*)?. No, but you can use a spare machin

Re: Archiving Data to Another DB?

On 04/11/2018 11:15 AM, Don Seiler wrote: Let's say I have two DBs: main (9.6.6) and archive (10.2). I have a table in main where I want to archive data older then 60 days. For various reasons, the table is not partitioned, so for now we must use DELETE. The destination table in the archive

Re: how to securely delete the storage freed when a table is dropped?

On 04/13/2018 12:48 PM, Jonathan Morgan wrote: For a system with information stored in a PostgreSQL 9.5 database, in which data stored in a table that is deleted must be securely deleted (like shred does to files), and where the system is persistent even though any particular table likely won

Re: how to securely delete the storage freed when a table is dropped?

dexes - new pages, or overwrite the existing page? And is any NPI (Non-Public-Info) data in the index itself? * So any PSQL core-engine guys reading? O. On Apr 13, 2018, at 3:03 PM, Ron wrote: On 04/13/2018 12:48 PM, Jonathan Morgan wrote: For a system with information stored in a Postg

pg_dump to a remote server

We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump file will be more than 1TB, and there's not enough disk space on the current system for the dump file. Thus, how can I send the pg_dump file directly to the new server while the pg_dump command is running?  NFS is one me

Re: pg_dump to a remote server

On 04/16/2018 07:47 PM, Gao Jack wrote: -Original Message- From: Ron Sent: Tuesday, April 17, 2018 7:59 AM To: pgsql-general Subject: pg_dump to a remote server We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump file will be more than 1TB, and there's

Re: pg_dump to a remote server

On 04/16/2018 07:18 PM, Adrian Klaver wrote: On 04/16/2018 04:58 PM, Ron wrote: We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump file will be more than 1TB, and there's not enough disk space on the current system for the dump file. Thus, how can I send t

Re: pg_dump to a remote server

On 04/16/2018 11:07 PM, Adrian Klaver wrote: On 04/16/2018 06:43 PM, Ron wrote: On 04/16/2018 07:18 PM, Adrian Klaver wrote: On 04/16/2018 04:58 PM, Ron wrote: We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump file will be more than 1TB, and there's not e

A couple of pg_dump questions

$ pg_dump --host=farawaysrvr -Fc $REMOTEDB > /local/disk/backups/$REMOTEDB.dump Is the data compressed on the remote server (thus minimizing traffic on the wire), or locally?  (I'd test this myself, but the company has really strict firewall rules in place.) $ pg_dump --host=farawaysrvr -Fc

Re: Postgresql database encryption

On 04/20/2018 03:55 PM, Vick Khera wrote: On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma > wrote: Hello Guys, Could someone throw light on the postgresql instance wide or database wide encryption please? Is this possible in postgresql and been in use i

Re: Postgresql database encryption

On 04/20/2018 06:11 PM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: On 04/20/2018 03:55 PM, Vick Khera wrote: On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma Someone really needs to explain that to me. My company-issued laptop has WDE, and that's great for

Re: Postgresql database encryption

On 04/20/2018 10:24 AM, Vikas Sharma wrote: Hello Guys, Could someone throw light on the postgresql instance wide or database wide encryption please? Is this possible in postgresql and been in use in production?. What about encrypted backups? -- Angular momentum makes the world go 'round.

Re: Postgresql database encryption

Also, Percona (a MySQL fork) 5.7. On 04/20/2018 07:31 PM, Ozz Nixon wrote: PS. the following database servers do offer internal encryption on a page/block oriented read/write (for encrypted data at rest security requirements) PremierSQL TDE MariaDB 10.1.3+ *MySQL* 5.7.11+ Microsoft uses TDE

Re: Rationale for aversion to the central database?

On 04/27/2018 05:52 PM, g...@luxsci.net wrote: On April 24, 2018 07:27:59 am PDT, "Sam Gendler" wrote: On Sun, Apr 8, 2018 at 15:37 g...@luxsci.net mailto:g...@luxsci.net>> wrote: On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" mailto:guy...@gmail.com>> wrote

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

On 05/02/2018 04:49 PM, David G. Johnston wrote: [snip] - the microsoft patented CSV would be required for implementation. it handles special data with commas and double-quotes in them ​If true this seems like a show-stopper to anything PostgreSQL would implement If MSFT really holds

pg_dump with compressible and non-compressible tables

Hi, v9.6 We've got big databases where some of the tables are highly compressible, but some have many bytea fields containing PDF files. When the data format is custom, directory or tar, how feasible would a "--no-blob-compression" option be (where pg_dump just tells the zlib library to jus

Re: pg_dump with compressible and non-compressible tables

On 05/05/2018 12:13 PM, Adrian Klaver wrote: On 05/05/2018 07:14 AM, Ron wrote: Hi, v9.6 We've got big databases where some of the tables are highly compressible, but some have many bytea fields containing PDF files. Can you see a demonstrable difference? Very much so.  The ASCI

Re: Enhancement to psql command, feedback.

On 05/09/2018 02:59 AM, John McKown wrote: I just wanted to throw this out to the users before I made a complete fool of myself by formally requesting it. But I would like what I hope would be a minor change (enhancement) to the psql command. If you look on this page, https://wiki.postgresql.or

Re: posgresql.log

On 05/21/2018 04:40 PM, Bartosz Dmytrak wrote: Hi Gurus, Looking into my postgresql.log on one of my test servers I found scary entry: --2018-05-19 05:28:21-- http://207.148.79.161/post0514/post Connecting to 207.148.79.161:80... connected. HTTP request sent, awaiting response... 200 OK Len

Re: [GENERAL] Postgre compatible version with RHEL 7.5

On 05/23/2018 08:13 AM, Adrian Klaver wrote: On 05/23/2018 03:59 AM, Deepti Sharma S wrote: Hi David, “9.6.6 is compatible but not supported”, what does this means? For details see: https://www.postgresql.org/support/versioning/ Basically it is supported by the community, but keeping up to

Re: computing z-scores

On 05/24/2018 10:15 AM, Martin Mueller wrote: You construct a z-score for a set of values by subtracting the average from the value and dividing the result by the standard deviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation. In a second

Re: Question on disk contention

On 05/31/2018 08:52 AM, Melvin Davidson wrote: On Thu, May 31, 2018 at 1:13 AM, Charles Clavadetscher mailto:clavadetsc...@swisspug.org>> wrote: Hi Melvin As an answer to a previous post you wrote: "Also, your main problem is that when you have two exact same queries execut

Re: Insert UUID GEN 4 Value

On 05/31/2018 07:39 PM, tango ward wrote: On Thu, May 31, 2018 at 12:32 PM, tango ward > wrote: On Thu, May 31, 2018 at 12:18 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Wednesday, May 30, 2018, tango ward mailto:tangowar...

Re: Whither 1:1?

On 06/01/2018 12:25 PM, Guyren Howe wrote: On Jun 1, 2018, at 10:16 , Olivier Gautherot > wrote: You will get a benefit in terms of space only if the optional fields in the second table exist in a reduced number of instances - and the second table is significantly

Re: Code of Conduct plan

On 06/03/2018 04:54 PM, Berend Tober wrote: Tom Lane wrote: Two years ago, there was considerable discussion about creating a Code of Conduct for the Postgres community... We are now asking for a final round of community comments... I really like that this was included: "Any allegations that

Re: How to get postmaster shut down time in postgres?

On 06/04/2018 08:44 AM, pavan95 wrote: Hi Adrian/Melvin, Thanks for your prompt replies. Yeah, I'm aware of that way. But my requirement is to get the server shutdown time whenever that event occurs and insert into a table dynamically!! Is it possible? You want to trap the shutdown action and

Re: Code of Conduct plan

On 06/03/2018 07:57 PM, Jonathan S. Katz wrote: [snip] Anyway, a big +1 to the effort of everyone who worked on the CoC for the past several years. From feedback in other forums through the years, I know it does make a difference to have a code of conduct in terms of helping people to feel more w

Re: Code of Conduct plan

If there's been so much Bad Behavior that's so Weakened the Community, then someone's done an excellent job of hiding that Bad Behavior. On 06/04/2018 09:57 AM, Evan Macbeth wrote: I just want to chime in and thank all those who worked on this Code of Conduct. It's well thought out, and I'm per

Re: Pgagent is not reading pgpass file either in Windows or Linux.

I've noticed that .pgpass is case sensitive, so am not surprised that it also wouldn't note the difference between 127.0.0.1 and localhost. On 06/04/2018 05:31 PM, nageswara Bandla wrote: I have figured out the issue with pgAgent both in Windows and Linux. PgAgent seems to ignore pgpass.conf/.

Re: Code of Conduct plan

On 06/07/2018 04:55 AM, Gavin Flower wrote: [snip] The Americans often seem to act as though most people lived in the USA, therefore we should all be bound by what they think is correct! "You" are wearing a tee-shirt (or hoodie), blue jeans and Nikes, while eating a fast food hamburger, drinki

Re: Code of Conduct plan

On 06/08/2018 12:09 AM, Gavin Flower wrote: On 08/06/18 16:55, Ron wrote: On 06/07/2018 04:55 AM, Gavin Flower wrote: [snip] The Americans often seem to act as though most people lived in the USA, therefore we should all be bound by what they think is correct! "You" are wea

Re: Can you make a simple view non-updatable?

On 06/08/2018 04:17 AM, Ryan Murphy wrote: maybe it is time to overhaul the security concept. I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views.  However, if possible it would be nice to get an error message about t

Re: What does Natvie Posgres mean?

This, to me, is the true meaning of "native PostgreSQL" (as opposed to "stock PostgreSQL", which is uncustomized code).  However, if the job wanted post was written by an HR flunky, it could mean anything. On 06/12/2018 01:11 PM, Benjamin Scherrey wrote: In my experience it refers to *developm

Re: PostgreSQL Volume Question

On 06/15/2018 11:26 AM, Data Ace wrote: Well I think my question is somewhat away from my intention cause of my poor understanding and questioning :( Actually, I have 1TB data and have hardware spec enough to handle this amount of data, but the problem is that it needs too many join operatio

Re: using pg_basebackup for point in time recovery

On 06/21/2018 12:27 AM, Michael Paquier wrote: [snip] Attached is a patch which includes your suggestion. What do you think? As that's an improvement, only HEAD would get that clarification. You've *got* to be kidding. Fixing an ambiguously or poorly worded bit of *documentation* should obvi

Re: Return select statement with sql case statement

On 07/04/2018 07:48 AM, hmidi slim wrote: Hi, I need to use conditional expression in my query, So I want to make a query like this: select numberOfPremiumDays             case  when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then            

Re: Return select statement with sql case statement

On 07/04/2018 10:32 AM, hmidi slim wrote: Actually, I need the use of case because based on the numberOfPremiumDays there are different type of treatment: select numberOfPremiumDays             case  when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) - ('2018-11-01'::timestam

Re: Return select statement with sql case statement

On 07/04/2018 05:08 PM, Adrian Klaver wrote: On 07/04/2018 03:03 PM, Ron wrote: On 07/04/2018 10:32 AM, hmidi slim wrote: Actually, I need the use of case because based on the numberOfPremiumDays there are different type of treatment: select numberOfPremiumDays             case  when

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 03:21 PM, Christopher Browne wrote: I have built one that I call Mahout (https://github.com/cbbrowne/mahout) which has the merit of involving just two shell scripts, one of which is an auditing tool (pgcmp). It implements a "little language" to indicate dependencies between the SQL

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 03:39 PM, Ravi Krishna wrote: Where I work, the requirement to have rollback scripts is part of the ITIL requirement for Changes to have a backout procedure. Liquibase provides that ability, but IMO rollback for RDBMS is always bit tricky. Certain DDL operations can take long t

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 04:10 PM, Christopher Browne wrote: [snip] ITIL surely does NOT specify the use of database rollback scripts as THE SPECIFIED MECHANISM for a backout procedure. In practice, we tend to take database snapshots using filesystem tools, as that represents a backout procedure that will

Improving pg_dump performance

Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. The database has many large tables full of bytea columns containing pdf images, and so the dump file is going to be more than 2x larger than the

Re: Improving pg_dump performance

On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you can use the pg_dump from the newer version

Re: Improving pg_dump performance

On 07/23/2018 08:27 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 15:06 schrieb Ron: On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new

Re: Improving pg_dump performance

On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from a 9.6 system? Yes, you can do a parallel backup, but you won't be able to get a

Re: Improving pg_dump performance

On 07/23/2018 08:56 AM, Adrian Klaver wrote: On 07/23/2018 06:47 AM, Ron wrote: On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from

Re: Improving pg_dump performance

On 07/23/2018 09:11 AM, Andres Freund wrote: Hi, On 2018-07-23 02:23:45 -0500, Ron wrote: We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. Have you considered using pg_upgrade instead? Yes, b

Speccing a remote backup server

Hi, v9.6 backing up v8.4 Where does the gzip run (where the database lives, or the remote server where the pg_dump runs from)?  I ask this because I need to know how beefy to make the backup server.  (It'll just store backups for a version upgrade.) -- Angular momentum makes the world go 'ro

Order in which tables are dumped

Hi, v8.4 if it matters. It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after* "doc_image". Are there some other rules besides alphabetical sorting? -- Angular momentum makes the world go 'round.

Re: Order in which tables are dumped

On 07/25/2018 10:28 AM, Tom Lane wrote: Ron writes: It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after* "doc_image". Looks like standard C-locale (ASCII) sort order to me ... I hate spreads

Re: Order in which tables are dumped

On 07/25/2018 10:43 AM, Vick Khera wrote: On Wed, Jul 25, 2018 at 11:15 AM, Ron <mailto:ronljohnso...@gmail.com>> wrote: Hi, v8.4 if it matters. It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is

Re: Design of a database table

On 07/30/2018 09:37 AM, hmidi slim wrote: I'm trying to design a database table. First of all there are two alternatives: 1-) Divide the table into two tables and make a join. 2-) Design a single table. 1rst alternative: Create table data_periods( id serial primary key not null, period daterang

Settings for fast restores

Hi, http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html shared_buffers = 1/2 of what you'd usually set maintenance_work_mem = 1GB-2GB wal_level = minimal full_page_writes = off wal_buffers = 64MB checkpoint_segments = 256 or higher max_wal_senders = 0 wal_keep_segments = 0 How

Re: Settings for fast restores

On 08/01/2018 09:11 AM, Vick Khera wrote: On Wed, Aug 1, 2018 at 2:03 AM, Ron <mailto:ronljohnso...@gmail.com>> wrote: Hi, http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html <http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html>

ALTER TABLE .. SET STATISTICS

v9.6.9 For columns of type bytea which store image data (PDFs, JPGs, etc) would it speed up the ANALYZE process to SET STATISTICS = 0? That way, default_statistics_target could be cranked higher -- giving better statistics for needed columns -- without polluting pg_statistics with unneeded

Re: Copying data from a CSV file into a table dynamically

On 08/14/2018 08:38 AM, pavan95 wrote: Hi Adrian, I tried to use *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago" +%Y-%m-%d`_*.csv' WITH csv;"* But it resulted in an error. How to issue such that it is understandable by psql? And I am completely unaware of python &

Re: pg_basebackup failed to read a file

On 08/14/2018 11:14 AM, Tom Lane wrote: Mike Cardwell writes: pg_basebackup: could not get write-ahead log end position from server: ERROR:  could not open file "./postgresql.conf~": Permission denied Now, I know what this error means. There was a root owned file at "/var/lib/pgsql/10/data/p

Re: [External] Multiple COPY on the same table

Maybe he just has a large file that needs to be loaded into a table... On 08/20/2018 11:47 AM, Vijaykumar Jain wrote: Hey Ravi, What is the goal you are trying to achieve here. To make pgdump/restore faster? To make replication faster? To make backup faster ? Also no matter how small you split

pg_dump order of operation

Hi, In v8.4, I noticed that the tables seemed to be dumped in alphabetical order.  Not so much, though, in a multithreaded 9.6 dump of an 8.4 database; there's no pattern that I can discern. In what order does the 9.6 pg_dump dump tables? Thanks -- Angular momentum makes the world go 'round

Re: pg_dump order of operation

On 08/26/2018 10:24 AM, Tom Lane wrote: Ron writes: In v8.4, I noticed that the tables seemed to be dumped in alphabetical order. Not so much, though, in a multithreaded 9.6 dump of an 8.4 database; there's no pattern that I can discern. In what order does the 9.6 pg_dump dump table

Re: pg_dump order of operation

On 08/26/2018 01:42 PM, Tom Lane wrote: Ron writes: On 08/26/2018 10:24 AM, Tom Lane wrote: Ron writes: In what order does the 9.6 pg_dump dump tables? I don't believe the ordering rules have changed materially since 8.4; it's intended to be by object kind, and within th

Re: pg_dump order of operation

On 08/26/2018 02:44 PM, Tom Lane wrote: Ron writes: On 08/26/2018 01:42 PM, Tom Lane wrote: Perhaps I don't understand *your* question. What concrete problem are you having? I want to track the progress of pg_dump so as to estimate completion time. Well, if you don't use --job

dat names generated by pg_dump

Hi, I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog table; pg_class didn't seem to have the relevant data) of making the association. If relev

Re: dat names generated by pg_dump

On 09/02/2018 08:41 PM, Adrian Klaver wrote: On 09/02/2018 05:40 PM, Ron wrote: Hi, I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog

Re: dat names generated by pg_dump

On 09/02/2018 09:26 PM, Tom Lane wrote: Ron writes: I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog table; pg_class didn't seem t

Re: Barman versus pgBackRest

On 03/09/2018 08:56 AM, David Steele wrote: [snip] About pgBarman, I like : - be able restore on a remote server from the backup server This a good feature, and one that has been requested for pgBackRest. You can do this fairly trivially with ssh, however, so it generally hasn't been a big deal

Re: Barman versus pgBackRest

On 09/04/2018 07:14 AM, Thomas Poty wrote: > Do you just change the IP address of the "restore target"? Do you expect a typical restore command? I'm investigating barman and pgBackRest to replace our exitsing NetBackup system, so don't know what you mean by "typical restore command". Here ar

Re: Barman versus pgBackRest

sept. 2018 à 14:47, Ron <mailto:ronljohnso...@gmail.com>> a écrit : On 09/04/2018 07:14 AM, Thomas Poty wrote: > Do you just change the IP address of the "restore target"? Do you expect a typical restore command? I'm investigating barman and pgBac

Re: Barman versus pgBackRest

On 09/04/2018 09:24 AM, Joshua D. Drake wrote: On 09/04/2018 07:14 AM, Ron wrote: That was about barman, in the barman group.  This is asking about pgbackrest...  :) So: does pgbackrest have this ability which barman does not have? The "--db-include" option seems to indicate th

Re: Barman versus pgBackRest

On 09/04/2018 10:24 AM, Joshua D. Drake wrote: On 09/04/2018 07:52 AM, Ron wrote: On 09/04/2018 09:24 AM, Joshua D. Drake wrote: On 09/04/2018 07:14 AM, Ron wrote: That was about barman, in the barman group.  This is asking about pgbackrest...  :) So: does pgbackrest have this ability

Re: Barman versus pgBackRest

On 09/04/2018 10:51 AM, David Steele wrote: [snip] This will work, but I don't think it's what Ron is getting at. To be clear, it is not possible to restore a database into an *existing* cluster using pgBackRest selective restore. This is a limitation of PostgreSQL file-level backu

Re: PostgreSQL: Copy from File missing data error

You might want to try pg_bulkload, and have it kick out malformed rows. It's packaged for RHEL6 and above, plus various other distros. On 09/04/2018 01:13 PM, Holly Gibons wrote: I'm using PostgreSQL 9.0 via pgAdmin III I'm trying to build a PostgreSQL/PostGIS database using Entire country fi

Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

On 09/05/2018 12:39 PM, Raghavendra Rao J S V wrote: Hi All, We are using postgres 9.2 verstion database. Please let me know, how many max number of wal files in pg_xlog directory? What is the formul. I am seeing different formulas. Could you provide me which decides number of max WAL files i

Re: PostgreSQL intenal scheduler?

Maybe https://github.com/chanks/que is what you need. On 09/05/2018 02:35 PM, Thiemo Kellner wrote: I have seen pg_cron but it is not what I am looking for. It schedules tasks only by time. I am looking for a fifo queue. pg_cron neither prevents from simultaneous runs I believe. Quoting Tho

pgbackrest when data/base is symlinked to another volume

Hi, Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6/data $PGDATA/base -> /Database/9.6/base $PGDATA/pg_log -> /Database/9.6/pg_log $PGDATA/pg_xlog -> /Database/9.6/pg_xlog (I'm not just defining data_di

Re: pgbackrest when data/base is symlinked to another volume

On 09/07/2018 05:22 PM, David Steele wrote: Hi Ron, On 9/6/18 11:21 PM, Ron wrote: Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6/data $PGDATA/base -> /Database/9.6/base $PGDATA/pg_log -> /Da

Re: pgbackrest when data/base is symlinked to another volume

On 09/07/2018 05:22 PM, David Steele wrote: Hi Ron, On 9/6/18 11:21 PM, Ron wrote: Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6/data $PGDATA/base -> /Database/9.6/base $PGDATA/pg_log -> /Da

Volume partitioning (was Re: pgbackrest when data/base is symlinked to another volume)

On 09/08/2018 03:07 PM, David Steele wrote: On 9/7/18 8:47 PM, Ron wrote: On 09/07/2018 05:22 PM, David Steele wrote: On 9/6/18 11:21 PM, Ron wrote: Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6

Re: PG8.3->10 migration data differences

Then fix your field-based data comparing mechanism. On 09/11/2018 03:41 AM, Csaba Ragasits wrote: Hello, We would like to migrate from 8.3 to 10 version. We've hundreds databases with different structures. That reason we're working on an automatic data comparing process. I've found the foll

Re: PG9.1 migration to PG9.6, dump/restore issues

On 09/12/2018 08:55 AM, Scot Kreienkamp wrote: Hi Everyone, I am working on a migration from PG9.1 to PG9.6.  Hoping some people can chime in on my plans as I am running into some restore issues. We are upgrading to a new version of PG and migrating to new hardware with RHEL 7, so I am plan

Re: Select rows when all all ids of its children records matches

Maybe this: select p.id, p.name from posts p, posts_tags pt, tags t where t.id in (1, 2, 3)   and t.id = pt.tag_id   and pt.post_id = p.id; On 09/12/2018 10:23 AM, Arup Rakshit wrote: I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all

Re: Convert interval to hours

On 09/14/2018 11:10 AM, Steven Lembark wrote: On Fri, 14 Sep 2018 11:55:18 -0400 Peter Kleiner wrote: On Fri, Sep 14, 2018 at 11:51 AM David Gauthier wrote: Hi: In perl/DBI, I have code that's getting me an "age" which returns something like... "-17 days -08:29:35". How can I convert that t

Re: How to maintain the csv log files in pg_log directory only for past 30 days

On 09/28/2018 12:03 AM, Raghavendra Rao J S V wrote: Hi All, Log file will be generated in *csv* format at *pg_log* directory in our PostgreSQL. Every day we are getting one log file. We would like to maintain only max 30 days. Which setting need to modify by us in “postgresql.conf” in order

Re: COPY from a remote machine in Datastage

On 10/05/2018 09:18 AM, Ravi Krishna wrote: Hello, if you need to use COPY command from remote machine and you use some libpq bindings (aka ruby pg gem for example), you can use functions associated with COPY command (https://www.postgresql.org/docs/10/static/libpq-copy.html). They should be

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

On 10/10/2018 09:32 PM, Raghavendra Rao J S V wrote: Hi All, pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box. Is it taking "more time" because your database is bigger? -- Angu

Re: something weird happened - can select by column value although column value exist

On 10/11/2018 03:17 PM, Dmitry O Litvintsev wrote: Hi, Today the following happened: Found this error in my production log: < 2018-10-11 13:31:52.587 CDT >ERROR: insert or update on table "file" violates foreign key constraint "$1" < 2018-10-11 13:31:52.587 CDT >DETAIL: Key (volume)=(155303

Re: Vacuum and freeing dead rows

On 7/5/19 3:16 AM, Simon T wrote: Hi, I have a very heavily updated table in a Postgres 9.6.10 database with lots of disk bloat. Every row is updated about once a minute, and little to no inserts. Approx 18k rows total. The table has bloated from ~1700 KB to about 6 GB over a few weeks time. I'm

Re: Measuring the Query Optimizer Effect: Turning off the QO?

On 7/7/19 6:49 PM, Tom Mercha wrote: On 08/07/2019 01:46, Rob Sargent wrote: On Jul 7, 2019, at 5:22 PM, Tom Mercha wrote: Hi All As we know, a query goes through number of stages before it is executed. One of these stages is query optimization (QO). There are various parameters to try and i

Re: pg_dump and search_path

On 7/9/19 2:22 AM, Laurenz Albe wrote: On Mon, 2019-07-08 at 23:54 -0700, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path; search_path -- "staging, transient, pg_catalog" (1 row) I ran `pg_dump --schema-only` and the only re

Re: how to execute pgsql2shp exe in sql

On 7/14/19 10:54 PM, sunpeng wrote: Could I use the following in psql or in a sql : "D:\PostgreSQL\9.6\bin\pgsql2shp " This *should* work, though you might have to fiddle with the quotes: yourdb=> \! "D:\PostgreSQL\9.6\bin\pgsql2shp " -- Angular momentum makes the world go 'round.

Re: Why no CREATE TEMP MATERIALIZED VIEW ?

On 7/16/19 11:56 AM, David G. Johnston wrote: On Tue, Jul 16, 2019 at 9:29 AM Ivan Voras > wrote: Out of curiosity, since there's CREATE TEMP VIEW, any particular reason there's no CREATE TEMP MATERIALIZED VIEW? Seems like it could be similar to a temp table

Re: Queries on QMF to POSTGRE

On 7/23/19 12:30 AM, Oleksandr Shulgin wrote: -hackers +pgsql general On Sun, Jul 21, 2019 at 7:33 PM JVM . > wrote: I’m looking to convert QMF Queries , QMF forms and QMF procedure to the POSTGRESQL will it support a

Re: Hardware for writing/updating 12,000,000 rows per hour

On 7/26/19 2:56 PM, Arya F wrote: Would it be possible to achieve 12,000,000 writes/updates on a single server? If so what kind of hardware should I be looking for? That's only 3,333 modifications/second.  How big are your records? -- Angular momentum makes the world go 'round.

Re: How do I create a Backup Operator account ?

On 7/30/19 12:58 AM, Luca Ferrari wrote: On Tue, Jul 30, 2019 at 2:50 AM Marcos Aurelio Nobre wrote: I was wondering if it was possible to configure the pgAdmin4 menus to be available for a particular login, only Backup & Restore items. But I'm not sure how to associate a bank login account w

Re: adding more space to the existing server

On 7/31/19 5:21 PM, Julie Nishimura wrote: Hello postgres folks, We're tossing around the idea of upgrading a replicated postgres cluster (37 dbs) by breaking the replication, adding different size (larger) data disks to the hot-spare, then turning replication back on, letting it fully popula

Re: PGAdmin4.11.1 on Ubuntu 18.04

I think the real question is "how do I make pgadmin4 use the locally installed psycopg instead of the system version?" On 8/1/19 10:32 AM, Tony Shelver wrote: The article at stackoverflow is here On Thu

Re: Compression In Postgresql 9.6

On 8/5/19 1:30 AM, Shital A wrote: Hello, Need inputs on below: We are working on a setting up a new highly transactional (tps 100k) OLTP system for payments using blockchain and postgresql 9.6 as DB on Rhel 7.6. Postgres version is 9.6 and not latest because of specs of blockchain component

  1   2   3   4   5   6   7   8   9   10   >