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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

[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: [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

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

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 ?

[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

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

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

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 > >

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

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

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:

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

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] 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 -

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

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]

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

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 ...

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

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

[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

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

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?

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] 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

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] 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

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

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

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 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

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

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

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

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

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. >

[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:

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

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? >

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

[GENERAL] Logcal replication in large scale

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? I think about map data from national mapping authorities. The problem is to get the updates of

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

>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 to bring down the master database because the server's disk