Re: [GENERAL] Logical decoding client has the power to crash the server

On Wed, Sep 20, 2017 at 3:14 PM, Meel Velliste wrote: > From what I understand about logical decoding, there is no limit to how many > log entries will be retained by the server if nobody reads them from the > logical slot. This means that a client that fails to read from the slot has > the power

Re: [GENERAL] Logcal replication in large scale

On Wed, Sep 20, 2017 at 8:53 AM, Nicklas Avén wrote: > Hallo all > > I am thrilled about logical replication in PostgreSQL 10. My head have > started spinning about use cases. > > Would it be possible to use logical replication as a distribution > method of data? > As an answer to the generic q

Re: [GENERAL] Final pg_dumpall should happen in Single-User-Mode

Just for the records, I asked here the same question and got some replies: https://dba.stackexchange.com/questions/186045/how-do-i-prevent-changes-to-my-database Am 11.09.2017 um 15:25 schrieb Thomas Güttler: I did a stupid mistake. I run the final pg_dumpall without switching to single-user-m

Re: [GENERAL] Logcal replication in large scale

On Wed, 2017-09-20 at 09:44 +0200, Magnus Hagander wrote: > On Wed, Sep 20, 2017 at 8:53 AM, Nicklas Avén g.no> wrote: > > Hallo all > > > > > > > > I am thrilled about logical replication in PostgreSQL 10. My head > > have > > > > started spinning about use cases. > > > > > > > > Would it

[GENERAL] VM-Ware Backup of VM safe?

We run a PostgreSQL 9.6 server in a virtual machine. The virtual machine is managed by the customer. He does backup the VM. Is this enough, is this safe? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programm

Re: [GENERAL] random row from a subset

On 09/20/2017 02:33 AM, Peter Koukoulis wrote: > > I'm attempting to get a random, based on a range that spans 1 to the > maximum number of rows that for a subset. > I run the query in Oracle sucessfully and get a different number each > time and only a single number, which is what I am expectin

Re: [GENERAL] VM-Ware Backup of VM safe?

Thomas Güttler wrote: > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? I don't know about VMware, but the general rule is that if the backup is truly atomic (it is guaranteed t

Re: [GENERAL] Logcal replication in large scale

On Wed, Sep 20, 2017 at 10:02 AM, Nicklas Avén wrote: > On Wed, 2017-09-20 at 09:44 +0200, Magnus Hagander wrote: > > On Wed, Sep 20, 2017 at 8:53 AM, Nicklas Avén > wrote: > > Hallo all > > I am thrilled about logical replication in PostgreSQL 10. My head have > started spinning about use cases

Re: [GENERAL] random row from a subset

thanks, interestingly your method works in both Oracle and PostgreSQL, albeit with a different random function call. It does not work in SQL Anywhere though. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] VM-Ware Backup of VM safe?

On Wed, 20 Sep 2017 10:03:15 +0200 Thomas Güttler wrote: > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? There are so many variables involved with doing that ... I don't thi

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

Hello, I appreciate your comments, thank you

Re: [GENERAL] VM-Ware Backup of VM safe?

On 9/20/17 7:36 AM, PT wrote: > On Wed, 20 Sep 2017 10:03:15 +0200 > Thomas Güttler wrote: > >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > > There are so many variab

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > > chiru r writes: > > > > We are looking for User profiles in ope source PostgreSQL. > > > > For example, If a user password failed n+

Re: [GENERAL] JDBC: logical replication and LSN feedback

+list First off you are going to get considerably better response from the JDBC list or our github project. Looking at the code; in order to ensure the backend has received the acknowledgement you need to call forceUpdateStatus Otherwise it may not receive the ack Dave Cramer da...@post

Re: [GENERAL] random row from a subset

On 09/20/2017 01:28 PM, bluefrog wrote: > > thanks, interestingly your method works in both Oracle and PostgreSQL, > albeit with a different random function call. > It does not work in SQL Anywhere though. > You will have to ask SQL Anywhere people, I guess. cheers -- Tomas Vondra

Re: [GENERAL] VM-Ware Backup of VM safe?

Il 20/09/2017 13:36, PT ha scritto: On Wed, 20 Sep 2017 10:03:15 +0200 Thomas Güttler wrote: We run a PostgreSQL 9.6 server in a virtual machine. The virtual machine is managed by the customer. He does backup the VM. Is this enough, is this safe? There are so many variables involved with d

Re: [GENERAL] JDBC: logical replication and LSN feedback

Thanks, I did not realize that acknowledgements are delayed until the next forceUpdateStatus() call. From the javadocs, I understood that the a read() call also does a forceUpdateStatus() internally, so a manual forceUpdateStatus() is not required. Maybe a forceUpdateStatus() only happens during a

Re: [GENERAL] libpq confusion

Hi, guys, On Wed, Sep 20, 2017 at 12:20 AM, Allan Harvey wrote: > >>How do I properly check if the record exists from libpq? > > Igor, > I use PQntuples() to check the number of ... tuples, for > 0 I was actually curious - isn't it what "PGRES_COMMAND_OK" for? IIUC, this constant indicates succe

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

John, * John R Pierce (pie...@hogranch.com) wrote: > On 9/19/2017 3:32 PM, chiru r wrote: > >How those application accounts get recognized in database? > > > >Let say  App_user1 authenticated through application ,after that > >how the App_user1 get access to DB? > > > >can you please provide more

[GENERAL] Inserting millions of record in a partitioned Table

Hi guys, with Postgresql 9.6.1 we need to insert, from a certain select query, some millions of record in a partitioned table. The table is partitioned by day. The datas we will import can, often, be mixed between two different days. We noticed that if we import directly into the global table it

Re: [GENERAL] libpq confusion

>How do I properly check if the record exists from libpq? Igor, I use PQntuples() to check the number of ... tuples, for > 0 Allan __ This e-mail message may contain confidential or legally privileged information and is only f

Re: [GENERAL] VM-Ware Backup of VM safe?

On Wed, 20 Sep 2017 10:03:15 +0200, Thomas Güttler wrote: >We run a PostgreSQL 9.6 server in a virtual machine. >The virtual machine is managed by the customer. >He does backup the VM. > >Is this enough, is this safe? It is "safe" if the VM is shut down first or if the backup is by copying a poi

Re: [GENERAL] Insert large number of records

On Tuesday, September 19, 2017, Job wrote: > and would not care about table partitioning (COPY command fire > partitioned-table triggers). You might want to write a script that inserts directly into the partitions and bypass routing altogether. Insert into ... select from ... is your only opti

Re: [GENERAL] VM-Ware Backup of VM safe?

> -Ursprüngliche Nachricht- > Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von Thomas Güttler > Gesendet: Mittwoch, 20. September 2017 10:03 > An: pgsql-general@postgresql.org > Betreff: [GENERAL] VM-Ware Backup of VM safe? > > We run a P

Re: [SPAM] Re: [GENERAL] VM-Ware Backup of VM safe?

Il 20/09/2017 17:15, Klaus P. Pieper ha scritto: -Ursprüngliche Nachricht- Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] Im Auftrag von Thomas Güttler Gesendet: Mittwoch, 20. September 2017 10:03 An: pgsql-general@postgresql.org Betreff: [GENERAL] VM

Re: [GENERAL] Insert large number of records

On 20 September 2017 at 07:42, Job wrote: > We use a "temporary" table, populated by pg_bulkload - it takes few minutes > in this first step. > Then, from the temporary table, datas are transferred by a trigger that copy > the record into the production table. > But *this step* takes really lots

Re: [GENERAL] libpq confusion

On 9/20/2017 6:30 AM, Igor Korot wrote: Hi, guys, On Wed, Sep 20, 2017 at 12:20 AM, Allan Harvey wrote: How do I properly check if the record exists from libpq? Igor, I use PQntuples() to check the number of ... tuples, for > 0 I was actually curious - isn't it what "PGRES_COMMAND_OK" for?

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

On 9/20/2017 6:55 AM, Stephen Frost wrote: If AD is in the mix here, then there's no need to have things happening at the database level when it comes to passwords- configure PG to use Kerberos and create a princ in AD and put that on the database server and then users can authenticate that way.

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

Greetings John, * John R Pierce (pie...@hogranch.com) wrote: > On 9/20/2017 6:55 AM, Stephen Frost wrote: > >If AD is in the mix here, then there's no need to have things happening > >at the database level when it comes to passwords- configure PG to use > >Kerberos and create a princ in AD and put

Re: [GENERAL] VM-Ware Backup of VM safe?

On Wed, 20 Sep 2017 17:15:36 +0200, "Klaus P. Pieper" wrote: >> -Ursprüngliche Nachricht- >> Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] Im Auftrag von Thomas Güttler >> Gesendet: Mittwoch, 20. September 2017 10:03 >> An: pgsql-general@postgresq

Re: [GENERAL] libpq confusion

Hi, John, On Wed, Sep 20, 2017 at 12:02 PM, John R Pierce wrote: > On 9/20/2017 6:30 AM, Igor Korot wrote: > > Hi, guys, > > On Wed, Sep 20, 2017 at 12:20 AM, Allan Harvey > wrote: > > How do I properly check if the record exists from libpq? > > Igor, > I use PQntuples() to check the number of .

Re: [GENERAL] libpq confusion

On 9/20/2017 10:34 AM, Igor Korot wrote: >From the documentation: https://www.postgresql.org/docs/9.1/static/libpq-exec.html [quote] PGRES_COMMAND_OK Successful completion of a command returning no data. [/quote] No data = no rows, right? from that same page, a bit farther down, clarifying t

Re: [GENERAL] libpq confusion

Thx. So it is referring to the command not a "command returning no data". ;-) On Wed, Sep 20, 2017 at 1:42 PM, John R Pierce wrote: > On 9/20/2017 10:34 AM, Igor Korot wrote: > > >From the documentation: > https://www.postgresql.org/docs/9.1/static/libpq-exec.html > > [quote] > PGRES_COMMAND_OK >

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

Thanks Andres! See inline... Andres Freund writes: > Hi, > > On 2017-09-19 17:00:05 -0500, Jerry Sievers wrote: >> Briefly, just curious if legacy max values for shared_buffers have >> scaled up since 8G was like 25% of RAM? > > It's very workload dependent. I've successfully used PG with rough

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

Ron Johnson writes: > On 09/19/2017 05:00 PM, Jerry Sievers wrote: > [snip] > >> The DB is 10TB total size with OLTP plus some occasional heavy batching >> which frequently correlates with degradation that requires intervention. >> >> Unrelated server problem forced us to relocate from a Debian/W

[GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

Basically as per $subject. We took a perf hit moving up to newer hardware and OS version which might in some cases be OK but admittedly there is some risk running a much older app (Pg 9.3) on a kernel/OS version that nowhere near existed when 9.3 was current. Be curious to hear of issues encounte

Re: [GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

On Wed, Sep 20, 2017 at 01:14:14PM -0500, Jerry Sievers wrote: > Be curious to hear of issues encountered and particular to eager to know > if disabling any kernel 4.x features helped. What was the old kernel/OS ? wheezy / kernel 3.x ? Perhaps try these ideas ? https://www.postgresql.org/message

Re: [GENERAL] VM-Ware Backup of VM safe?

> Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von George Neuner > > But VSS is needed only to copy VM files *while* they are in use. If you > snapshot > the VM, the snapshot files then are read-only and can be freely copied. As > long > as t

Re: [SPAM] Re: [GENERAL] VM-Ware Backup of VM safe?

> -Ursprüngliche Nachricht- > Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Im Auftrag von Moreno Andreo > Gesendet: Mittwoch, 20. September 2017 17:42 > I may be wrong, as I don't know MS SQL Server, but in the way you describe it, > you make me com

[GENERAL] Puzzled by UNION with unknown types

Hi. I've solved my practical problems, and I know unknown types are just bad, but am still curious about why some of these cause errors, and others don't. It seems contingent on whether the value is generated on the fly, but I don't understand the mechanism or distinction. Any help appreciated!

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

On 2017-09-20 13:00:34 -0500, Jerry Sievers wrote: > >> Pg 9.3 on monster 2T/192 CPU Xenial thrashing > > > > Not sure what the word "thrashing" in that sentence means. > > Cases of dozens or hundreds of sessions running typical statements for > this system but running 100% on their CPUs. Seems t

Re: [GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

On Wed, Sep 20, 2017 at 12:14 PM, Jerry Sievers wrote: > Basically as per $subject. > > We took a perf hit moving up to newer hardware and OS version which > might in some cases be OK but admittedly there is some risk running a > much older app (Pg 9.3) on a kernel/OS version that nowhere near exi

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

On Tue, Sep 19, 2017 at 4:00 PM, Jerry Sievers wrote: > Briefly, just curious if legacy max values for shared_buffers have > scaled up since 8G was like 25% of RAM? > > Pg 9.3 on monster 2T/192 CPU Xenial thrashing > > Upgrade pending but we recently started having $interesting performance > issue

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

On 09/20/2017 01:05 PM, Jerry Sievers wrote: Ron Johnson writes: On 09/19/2017 05:00 PM, Jerry Sievers wrote: [snip] The DB is 10TB total size with OLTP plus some occasional heavy batching which frequently correlates with degradation that requires intervention. Unrelated server problem forc

Re: [GENERAL] Puzzled by UNION with unknown types

Ken Tanzer writes: > Hi. I've solved my practical problems, and I know unknown types are > just bad, but am still curious about why some of these cause errors, > and others don't. > ag_consulting=> SELECT * FROM (SELECT '1'::unknown) foo UNION ALL > SELECT * FROM (SELECT '1'::unknown) bar; > ERR

Re: [GENERAL] Inserting millions of record in a partitioned Table

On Wed, Sep 20, 2017 at 10:10 AM, Job wrote: > We noticed that if we import directly into the global table it is really, > really slow. > Importing directly in the single partition is faster. > > Do you have a rule or trigger on the main table to redirect to the partitions? You should expect that

Re: [GENERAL] Inserting millions of record in a partitioned Table

On 09/20/2017 02:46 PM, Vick Khera wrote: On Wed, Sep 20, 2017 at 10:10 AM, Job > wrote: We noticed that if we import directly into the global table it is really, really slow. Importing directly in the single partition is faster. Do you have a rul

R: [GENERAL] Insert large number of records

> Even better would be if your bulkload could already be organised such > that all the data in the "temporary" table can indiscriminately be > inserted into the same target partition. That though depends a bit on > your setup - at some point the time saved at one end gets consumed on > the other o

Re: [GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

Scott Marlowe writes: > On Wed, Sep 20, 2017 at 12:14 PM, Jerry Sievers > wrote: > >> Basically as per $subject. >> >> We took a perf hit moving up to newer hardware and OS version which >> might in some cases be OK but admittedly there is some risk running a >> much older app (Pg 9.3) on a ker

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

Scott Marlowe writes: > On Tue, Sep 19, 2017 at 4:00 PM, Jerry Sievers wrote: > >> Briefly, just curious if legacy max values for shared_buffers have >> scaled up since 8G was like 25% of RAM? >> >> Pg 9.3 on monster 2T/192 CPU Xenial thrashing >> >> Upgrade pending but we recently started havin

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

Andres Freund writes: > On 2017-09-20 13:00:34 -0500, Jerry Sievers wrote: > >> >> Pg 9.3 on monster 2T/192 CPU Xenial thrashing >> > >> > Not sure what the word "thrashing" in that sentence means. >> >> Cases of dozens or hundreds of sessions running typical statements for >> this system but ru

Re: [GENERAL] Puzzled by UNION with unknown types

Thanks Tom! -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ * *https://agency-software.org/demo/client * ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing

Re: [GENERAL] VM-Ware Backup of VM safe?

On Wed, Sep 20, 2017 at 5:45 PM, Albe Laurenz wrote: > Thomas Güttler wrote: >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > > I don't know about VMware, I heard about

Re: [GENERAL] Logical decoding client has the power to crash the server

Hi Michael, Thank you, I appreciate your response. Now that you mention, I am realizing that I don't really care about dropping the oldest log entries. Mandatory monitoring makes a lot of sense and dropping the entire slot would be perfect when it consumes too much space. The only problem with mo

Re: [GENERAL] Logical decoding client has the power to crash the server

On Thu, Sep 21, 2017 at 1:09 PM, Meel Velliste wrote: > In this situation, neither us, nor our customer has the power to install the > required monitoring of pg_xlog. The database hosting provider would have to > do it. In most cases (e.g. Amazon RDS) the hosting provider does provide a > way of m

Re: [GENERAL] 10 beta 4 foreign table partition check constraint broken?

On Fri, Sep 15, 2017 at 10:43 PM, Paul Jones wrote: > Is this a bug in Postgres 10b4? Looks like neither partition ranges > nor check constraints are honored in 10b4 when inserting into > partitions that are foreign tables. Here is what you are looking for in the documentation: https://www.postg

Re: [GENERAL] a JOIN to a VIEW seems slow

On 2017-09-18 Frank Millman wrote: > > Here are the timings for running the query on identical data sets using > Postgresql, Sql Server, and Sqlite3 - > > PostgreSQL - > Method 1 - 0.28 sec > Method 2 – 1607 sec, or 26 minutes > > Sql Server - > Method 1 – 0.33 sec > Method 2 –