Re: [GENERAL] Help on Index only scan

=?utf-8?Q?Ertan_K=C3=BC=C3=A7=C3=BCko=C4=9Flu?= writes: >>> I want to have an index only scan for my below query: >>> select autoinc, fileversion from updates where filename = 'Robox.exe' order >>> by autoinc desc; >> On 14 Aug 2017, at 01:15, Melvin Davidson wrote: >> As far as "Index only sca

Re: [GENERAL] Help on Index only scan

On Sun, Aug 13, 2017 at 7:37 PM, Ertan Küçükoğlu < ertan.kucuko...@1nar.com.tr> wrote: > > On 14 Aug 2017, at 01:15, Melvin Davidson wrote: > > > On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu < > ertan.kucuko...@1nar.com.tr> wrote: > >> Hello, >> >> My table details: >> robox=# \dS+ updates >>

Re: [GENERAL] Help on Index only scan

> On 14 Aug 2017, at 01:15, Melvin Davidson wrote: > > >> On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu >> wrote: >> Hello, >> >> My table details: >> robox=# \dS+ updates >>Table "public.updates" >> Column | Type |

Re: [GENERAL] Help on Index only scan

On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu < ertan.kucuko...@1nar.com.tr> wrote: > Hello, > > My table details: > robox=# \dS+ updates >Table "public.updates" > Column | Type | Modifiers > | Storage | Sta

[GENERAL] Help on Index only scan

Hello, My table details: robox=# \dS+ updates Table "public.updates" Column | Type | Modifiers | Storage | Stats target | Description ---+-+--

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Ken Tanzer writes: >> FWIW, the business with making and editing a list file should work just >> fine with a tar-format dump, not only with a custom-format dump. The >> metadata is all there in either case. > The pg_dump doc page kinda suggests but doesn't quite say that you can't > re-order tar

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 06/05/2017 05:59 PM, Ken Tanzer wrote: Not sure why just know that if I stay within the guidelines it works, if I do not its does not work:) That's fair enough, leaving aside the curiosity part. Usually though the things you can't do just aren't allowed. It's easier to overlook

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > > So I can switch to Custom format for future backups. But regarding the > > existing backups I have in Tar format, is there any way to successfully > > restore them? > > FWIW, the business with making and editing a list file should work just > fine with a tar-format dump, not only with a cust

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Ken Tanzer writes: > ...The rest of the DB is fine, but tbl_payment has 0 rows. I believe this is > because tbl_payment has a constraint that calls a function has_perm() that > relies on data in a couple of other tables, and that tbl_payment is being > restored before those tables. I was able to

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 6:21 PM, Ken Tanzer wrote: > I do get the "make \d show relevant information" argument and that is one >> that seems easier to solve... >> > > Maybe I'm missing something, but I'm not sure how you'd solve this or > change what \d shows for a table. Right now I get to see t

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > I do get the "make \d show relevant information" argument and that is one > that seems easier to solve... > Maybe I'm missing something, but I'm not sure how you'd solve this or change what \d shows for a table. Right now I get to see this in my \d: "authorized_approvers_only" CHECK (approve

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 5:59 PM, Ken Tanzer wrote: > I can't really make this an FK. I can (and probably will) put this into a >>> trigger. Although it seems like an extra layer of wrapping just to call a >>> function. I'm curious if there's any conceptual reason why constraints >>> couldn't (a

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > I can't really make this an FK. I can (and probably will) put this into a >> trigger. Although it seems like an extra layer of wrapping just to call a >> function. I'm curious if there's any conceptual reason why constraints >> couldn't (as an option) be restored after all the data is loaded

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 06/05/2017 05:15 PM, Ken Tanzer wrote: Thanks Adrian and David. That all makes sense, and I gather the answer regarding the existing dumps is "no, they can't be restored." So be it. Here's a couple of follow-on comments:: Ideally figure out how to write an actual FK constraint - othe

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 6/5/2017 5:49 PM, David G. Johnston wrote: On Mon, Jun 5, 2017 at 5:40 PM, John R Pierce >wrote: ​i​ ndeed, any sort of constraint that invokes a function call which looks at other tables could later be invalidated if those other tables change, and

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 5:40 PM, John R Pierce wrote: > ​i​ > ndeed, any sort of constraint that invokes a function call which looks at > other tables could later be invalidated if those other tables change, and > postgres would be none the smarter. the same goes for trigger based > checks. > ​

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > Aside from being a bit more verbose there is nothing useful that writing > this as "CHECK function()" provides that you don't also get by writing > "CREATE TRIGGER". > I agree you get the same result. It may be a minor issue, but for me it is convenient to see the logic spelled out when using

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 6/5/2017 5:32 PM, David G. Johnston wrote: On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer >wrote: From the docs: https://www.postgresql.org/docs/9.6/static/sql-createtable.html

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer wrote: > From the docs: >> https://www.postgresql.org/docs/9.6/static/sql-createtable.html >> "Currently, CHECK expressions cannot contain subqueries nor refer to >> variables other than columns of the current row. The system column tableoid >> may be re

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 6/5/2017 5:15 PM, Ken Tanzer wrote: I can't really make this an FK. I can (and probably will) put this into a trigger. Although it seems like an extra layer of wrapping just to call a function. I'm curious if there's any conceptual reason why constraints couldn't (as an option) be restore

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Thanks Adrian and David. That all makes sense, and I gather the answer regarding the existing dumps is "no, they can't be restored." So be it. Here's a couple of follow-on comments:: Ideally figure out how to write an actual FK constraint - otherwise use > triggers. I can't really make this an

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 06/05/2017 03:35 PM, Ken Tanzer wrote: On 9.3.17, I tried to restore a tar from pg_dump. It failed thusly: bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 10

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 3:35 PM, Ken Tanzer wrote: > I believe this is because tbl_payment has a constraint that calls a > function has_perm() that relies on data in a couple of other tables > ​Indeed this is the cause. That configuration is not supported. If you need to lookup values in other

[GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 9.3.17, I tried to restore a tar from pg_dump. It failed thusly: bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 10608; 0 107743 TABLE DATA tbl_payment spc pg_

Re: [GENERAL] Help with terminology to describe what my software does please?

>> Cluster comparison would only occur if you have two or more clusters on >> the same server, although it's possible to compare across servers, > > > Explain, because as I understand it a server = one cluster: > I think he was using server in the server=one machine sense, ie a single machine/serv

Re: [GENERAL] Help with terminology to describe what my software does please?

> > > Cluster comparison would only occur if you have two or more clusters on > the same server, although it's possible to compare across servers, > but that would involve a lot more work. AFAIK, the only differences for a > cluster would be: > 1. PostgreSQL version > 2. path to database > 3. datab

Re: [GENERAL] Help with terminology to describe what my software does please?

On 05/28/2017 07:53 AM, Melvin Davidson wrote: Cluster comparison would only occur if you have two or more clusters on the same server, although it's possible to compare across servers, Explain, because as I understand it a server = one cluster: https://www.postgresql.org/docs/9.6/s

Re: [GENERAL] Help with terminology to describe what my software does please?

Neil Anderson writes: > I guess I don't know what is the most common way to say that it > compares everything but the data. Any suggestions from your > experience? FWIW, I think it's pretty common to use "schema" in an abstract way to mean "the structure of your database", ie everything but the d

Re: [GENERAL] Help with terminology to describe what my software does please?

On Sun, May 28, 2017 at 9:51 AM, Adrian Klaver wrote: > On 05/28/2017 05:49 AM, Neil Anderson wrote: > >> Hi, >> >> I'm working on a tool that can compare the properties of Postgres >> objects from different instances, finding the differences and >> outputting the update SQL. >> >> It can compare

Re: [GENERAL] Help with terminology to describe what my software does please?

On 05/28/2017 05:49 AM, Neil Anderson wrote: Hi, I'm working on a tool that can compare the properties of Postgres objects from different instances, finding the differences and outputting the update SQL. It can compare objects that are defined at the cluster, database or schema level. As such I

[GENERAL] Help with terminology to describe what my software does please?

Hi, I'm working on a tool that can compare the properties of Postgres objects from different instances, finding the differences and outputting the update SQL. It can compare objects that are defined at the cluster, database or schema level. As such I'm finding it difficult to describe what the to

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Magnus Hagander writes: > On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz wrote: >> Not sure whether we should *fix* this or not on RPM side. This may break >> some of the existing installations, right? > Changing that in a minor version seems like a *really* bad idea, because > things *will* br

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On 05/16/2017 01:00 AM, Devrim Gündüz wrote: Hi, On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote: https://redmine.postgresql.org/issues/2409 Not sure whether we should *fix* this or not on RPM side. This may break some of the existing installations, right? I'm not objecting, just asking

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz wrote: > > Hi, > > On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote: > > https://redmine.postgresql.org/issues/2409 > > Not sure whether we should *fix* this or not on RPM side. This may break > some > of the existing installations, right? > > I'

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi, On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote: > https://redmine.postgresql.org/issues/2409 Not sure whether we should *fix* this or not on RPM side. This may break some of the existing installations, right? I'm not objecting, just asking for opinions. Regards, -- Devrim Gündüz Ente

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Mon, May 15, 2017 at 4:45 PM, Adrian Klaver wrote: > On 05/15/2017 01:40 PM, Ken Tanzer wrote: > > > >> But let me ask, is there a big warning about this somewhere I missed? >> Can the 9.2 updates do something to fix this, or at least create a warning >> or an RPMNEW file? I'm happy this is a

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On 05/15/2017 01:40 PM, Ken Tanzer wrote: But let me ask, is there a big warning about this somewhere I missed? Can the 9.2 updates do something to fix this, or at least create a warning or an RPMNEW file? I'm happy this is a cloud server and that I worked on a copy. However, in differen

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi, On Mon, 2017-05-15 at 16:34 -0400, Tom Lane wrote: > > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > > psql: could not connect to server: Connection refused > >    Is the server running locally and accepting > >    connections on Unix domain socket > > "/var/run/postgresql/.s.PGSQL.5432

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

> > >> Workarounds: >> >> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows >> the >> old socket directory. >> > > That was where I was going until I saw this in the OP: > > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > psql: could not connect to server: Connection refused >

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Adrian Klaver writes: > On 05/15/2017 01:10 PM, Devrim Gündüz wrote: >> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows the >> old socket directory. > That was where I was going until I saw this in the OP: > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > psql: could not con

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On 05/15/2017 01:10 PM, Devrim Gündüz wrote: Hi, On Mon, 2017-05-15 at 12:55 -0700, Ken Tanzer wrote: Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to install PGDG 9.6 alongside the already-running 9.2. After installing the 9.6 packages (and even before doing an initd

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi, On Mon, 2017-05-15 at 12:55 -0700, Ken Tanzer wrote: > Hi.  On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to > install PGDG 9.6 alongside the already-running 9.2.  After installing the > 9.6 packages (and even before doing an initdb), I am no > longer able to make a local

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Ken Tanzer writes: > Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to > install PGDG 9.6 alongside the already-running 9.2. After installing the > 9.6 packages (and even before doing an initdb), I am no > longer able to make a local connection to the 9.2 server. Instead

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Mon, May 15, 2017 at 12:55:48PM -0700, Ken Tanzer wrote: > Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to > install PGDG 9.6 alongside the already-running 9.2. After installing the > 9.6 packages (and even before doing an initdb), I am no > longer able to make a local

[GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to install PGDG 9.6 alongside the already-running 9.2. After installing the 9.6 packages (and even before doing an initdb), I am no longer able to make a local connection to the 9.2 server. Instead I get the message: psql: co

[GENERAL] Help regarding flushing data from Shared Memory to Database

Hi, We are currently working on cstore extension. We are storing the all the inserts in a shared memory and then flushing the tuples to the database. We are using a background worker to periodically flush the data. But the problem is we are not able to do the insert the data during the periodic che

[GENERAL] Help regarding flushing data from Shared Memory to Database

Hi, We are currently working on cstore extension. We are storing the all the inserts in a shared memory and then flushing the tuples to the database. We are using a background worker to periodically flush the data. But the problem is we are not able to do the insert the data during the periodic che

Re: [GENERAL] Help with Trigger

Thank you for your suggestion which solved the problem. Much better solution that what I was trying to accomplish. Much smaller table to query since it only has one entry per user. Clifford On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver wrote: > On 12/28/2016 07:06 PM, Clifford Snow wrote: > >>

Re: [GENERAL] Help with Trigger

On 12/28/2016 07:06 PM, Clifford Snow wrote: I'm trying to write a trigger (my first) to update another table if the user_id is new. But I'm getting a index exception that the user_id What is the actual error message? already exists. I'm picking up data from another feed which gives provides

[GENERAL] Help with Trigger

I'm trying to write a trigger (my first) to update another table if the user_id is new. But I'm getting a index exception that the user_id already exists. I'm picking up data from another feed which gives provides me with changes to the main database. what I have is CREATE OR REPLACE FUNCTION add

Re: [GENERAL] help with moving tablespace

> Bonus question: I found an ER diagram of some of the pg_* tables at h > ttp://www.slideshare.net/oddbjorn/Get-to-know-PostgreSQL. Is there an > ERD of all of them so a person can better understand how to use them > when one must? I suppose the same question applies to > information_schema since

Re: [GENERAL] help with moving tablespace

> On Thu, Nov 17, 2016 wrote: > > On Thu, Nov 17, 2016 at 9:16 AM, wrote: > > First, the above works only *most* of the time in our testing on multiple > > servers. When it fails, it's because not everything was moved out of the > > old tablespace and I don't understand why. An "ls $PGDATA/ourd

Re: [GENERAL] help with moving tablespace

On Thu, Nov 17, 2016 at 9:16 AM, wrote: > First, the above works only *most* of the time in our testing on multiple > servers. When it fails, it's because not everything was moved out of the > old tablespace and I don't understand why. An "ls $PGDATA/ourdb/PG*/" shows > files are still present. A

[GENERAL] help with moving tablespace

Back in the early dawn of time (before I came here :) the tablespace for our DB was put under the data dir, which gives us the warning "tablespace location should not be inside the data directory", which I'm trying to fix -- and I'm also trying to fix ownerships so everything isn't owned by post

Re: [GENERAL] Help with slow query - Pgsql 9.2

On Mon, Sep 5, 2016 at 6:53 PM, Patrick B wrote: > Hi guys, > > I got this query: > >> SELECT id,jobid,description,serialised_data >> FROM logtable >> WHERE log_type = 45 >> AND clientid = 24011 >> ORDER BY gtime desc > > What is really going to help you here is multicolumn index on (clientid, l

[GENERAL] Help with slow query - Pgsql 9.2

Hi guys, I got this query: > SELECT id,jobid,description,serialised_data > FROM logtable > WHERE log_type = 45 > AND clientid = 24011 > ORDER BY gtime desc Explain analyze: https://explain.depesz.com/s/XKtU So it seems the very slow part is into: -> Bitmap Index Scan on "ix_cl

Re: [GENERAL] Help on recovering my standby

I had the same issue... A slave server had missing wal_files... and it wasn't synced. I had to re-sync all the DB, by running the pg_basebackup command So.. basically, what I did is: 1 - Ensure that the wal_files are being inserted into the slave 2 - Backup the recovery.conf, postgresql.conf

Re: [GENERAL] Help on recovering my standby

On Wed, Jun 22, 2016 at 12:22 PM, Alan Hodgson wrote: > On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote: > > Hi I have my standby (streaming replication) down due to missing wal > files. > > You would see the same error in the logs stating "cannot find the wal > file > > ..." What

Re: [GENERAL] Help on recovering my standby

On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote: > Hi I have my standby (streaming replication) down due to missing wal files. > You would see the same error in the logs stating "cannot find the wal file > ..." What is the best way to get it going so that when we switch between > st

[GENERAL] Help on recovering my standby

Hi I have my standby (streaming replication) down due to missing wal files. You would see the same error in the logs stating "cannot find the wal file ..." What is the best way to get it going so that when we switch between standby and primary once in a while they are in sync? Currently I am wor

Re: [GENERAL] Help needed structuring Postgresql correlation query

Thanks for that, looks like something to sink my teeth into ! On 21 June 2016 at 13:29, Alban Hertroys wrote: > >> On 19 Jun 2016, at 10:58, Tim Smith wrote: >> >> Hi, >> >> My postgresql-fu is not good enough to write a query to achieve this >> (some may well say r is a better suited tool to ac

Re: [GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

Thank you David. -Allan. On Mon, Jun 20, 2016 at 11:19 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sun, Jun 19, 2016 at 5:09 PM, Allan Kamau wrote: > >> I have an xml document from which I would like to extract the contents of >> several elements. >> >> I would like to use x

Re: [GENERAL] Help needed structuring Postgresql correlation query

> On 19 Jun 2016, at 10:58, Tim Smith wrote: > > Hi, > > My postgresql-fu is not good enough to write a query to achieve this > (some may well say r is a better suited tool to achieve this !). > > I need to calculate what I would call a correlation window on a time > series of data, my table l

Re: [GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

On Sun, Jun 19, 2016 at 5:09 PM, Allan Kamau wrote: > I have an xml document from which I would like to extract the contents of > several elements. > > I would like to use xpath to extract the contents of "name" from the xml > document shown below. > > WITH x AS > ( > SELECT > ' > http://uniprot.

[GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

I have an xml document from which I would like to extract the contents of several elements. I would like to use xpath to extract the contents of "name" from the xml document shown below. WITH x AS ( SELECT ' http://uniprot.org/uniprot"; xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"; xsi:

[GENERAL] Help needed structuring Postgresql correlation query

Hi, My postgresql-fu is not good enough to write a query to achieve this (some may well say r is a better suited tool to achieve this !). I need to calculate what I would call a correlation window on a time series of data, my table looks like this : create table data(data_date date,data_measurem

Re: [GENERAL] HELP! Uninstalled wrong version of postgres

El 24/03/16 a las 14:19, Howard News escribió: Hi, I uninstalled the wrong version of postgres on Ubuntu using apt-get remove postgresql-9.0, convinced that this was an old unused version. You guess the rest... The data files still appear to be there, all 485GB of them. Can these be restored?

[GENERAL] HELP! Uninstalled wrong version of postgres

Hi, I uninstalled the wrong version of postgres on Ubuntu using apt-get remove postgresql-9.0, convinced that this was an old unused version. You guess the rest... The data files still appear to be there, all 485GB of them. Can these be restored? Thanks. -- Sent via pgsql-general mailing

[GENERAL] Help me get started with moving away from Firebird

I was able to create all the domains, tables, views, etc, but I have trouble creating stored procedures and triggers. Also, a question, does Postgres support events, for example in Firebird I could do something like this and then receive the event in the GUI: if ((NEW.MUSED_M_>=NEW.MLIMIT_M_) an

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/9/15 7:05 PM, Andreas Kretschmer wrote: I'm really newbie to PostgreSQL but the boss pushed me to handle it >and implement it in production f*&%*$%%$#%$#&# (forgive me) >They don't hire a database expert, I don't know why. You can learn that. PostgreSQL is really, really great. Btw.: i kno

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

> FattahRozzaq hat am 10. Dezember 2015 um 01:27 > geschrieben: > > > Hi John, > > I really don't know why I should keep the wal archives. That's the problem! But that's your part, not our. If you need a Backup with PITR-capability you have to create a so called basebackup and continously W

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/09/2015 04:27 PM, FattahRozzaq wrote: Hi John, I really don't know why I should keep the wal archives. So who set up the archiving and why? Is archive recovery set up on the standby?: http://www.postgresql.org/docs/9.4/interactive/archive-recovery-settings.html I implement streaming

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

Hi John, Really thanking you for spend time typing and responding my email. I think the archive_command returns success, I can see the archive directory piling up 16MB every 2 minutes. Maybe the pgarchivecleanup is the solution to cleanup the contents of archive folder? How to properly do it? What

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/9/2015 4:27 PM, FattahRozzaq wrote: I really don't know why I should keep the wal archives. I implement streaming replication into 1 server (standby server). I'm really newbie to PostgreSQL but the boss pushed me to handle it and implement it in production f*&%*$%%$#%$#&# (forgive me) They

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/09/2015 04:38 PM, FattahRozzaq wrote: Quick information, After I realize, the line "archive_command=/bin/true" is a bad decision, I have revert it back. Now I'm really confused and panic. I don't know what to do, and I don't really understand the postgresql.conf I'm a network engineer, I s

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

Quick information, After I realize, the line "archive_command=/bin/true" is a bad decision, I have revert it back. Now I'm really confused and panic. I don't know what to do, and I don't really understand the postgresql.conf I'm a network engineer, I should handle the network and also postgresql d

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

Hi John, I really don't know why I should keep the wal archives. I implement streaming replication into 1 server (standby server). I'm really newbie to PostgreSQL but the boss pushed me to handle it and implement it in production f*&%*$%%$#%$#&# (forgive me) They don't hire a database expert, I do

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/8/2015 4:55 PM, FattahRozzaq wrote: ...I want to limit the total size use by WAL archive to around 200-400 GB...? for what purpose are you keeping a wal archive ? if its for PITR (point in time recovery), you need ALL WAL records since the start of a base backup up to the point in time

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On 12/09/2015 11:15 AM, Alan Hodgson wrote: On Wednesday, December 09, 2015 07:55:09 AM FattahRozzaq wrote: archive_mode = on archive_command = 'cp -i %p /home/postgres/archive/master/%f' The WAL archive folder is at /home/postgres/archive/master/, right? This directory consumes around 750GB o

Re: [GENERAL] HELP!!! The WAL Archive is taking up all space

On Wednesday, December 09, 2015 07:55:09 AM FattahRozzaq wrote: > archive_mode = on > archive_command = 'cp -i %p /home/postgres/archive/master/%f' > > > The WAL archive folder is at /home/postgres/archive/master/, right? > This directory consumes around 750GB of Disk-1. > Each segment in the /ho

[GENERAL] HELP!!! The WAL Archive is taking up all space

Hi all, Please help... I have 1 master PostgreSQL and 1 standby PostgreSQL. Both servers has the same OS Linux Debian Wheezy, the same hardware. Both server hardware: CPU: 24 cores RAM: 128GB Disk-1: 800GB SAS (for OS, logs, WAL archive directory) Disk-2: 330GB SSD (for PostgreSQL data directory

Re: [GENERAL] Help me recovery databases.

Thank you very much. Well done. Backups it's all))  01.06.2015, 03:05, "Melvin Davidson" :If you have a pg_dumpall, or a pg_dump of your databases, you "might" be able to get your data back by doing the following.1. If your data directory is corrupted or still exists, rename it.2. Make copies of yo

Re: [GENERAL] Help me recovery databases.

If you have a pg_dumpall, or a pg_dump of your databases, you "might" be able to get your data back by doing the following. 1. If your data directory is corrupted or still exists, rename it. 2. Make copies of your postgresql.conf & pg_hba.conf if you still have them. 3. use initdb to recreate the

Re: [GENERAL] Help me recovery databases.

"base" is where all the data files are located, so the answer is most likely 'no'. On 05/31/15 15:11, Evi-M wrote: Good day, Anyone. I lost folders with /base pg_xlog and pg_clog mount another hard disk.(500gb) This is Postgresql 9.1, Ubuntu 12.04 Could i restore databases without /base? I have

[GENERAL] Help me recovery databases.

Good day, Anyone. I lost folders with /base pg_xlog and pg_clog mount another hard disk.(500gb)  This is Postgresql 9.1, Ubuntu 12.04 Could i restore databases without /base? I have archive_status folder.  -- С Уважением,Генералов Юрий 

Re: [GENERAL] Help with slow table update

On 4/19/15 9:53 PM, Tim Uckun wrote: On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov mailto:pawel.vese...@gmail.com>> wrote: I found some dangling prepared transactions How do you find and remove these? SELECT * FROM pg_prepared_xacts; ROLLBACK PREPARED xid; -- Jim Nasby, Data Architec

Re: [GENERAL] Help with slow table update

On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov wrote: > I found some dangling prepared transactions How do you find and remove these?

Re: [GENERAL] Help with slow table update

> > [skipped] > > >> But remember that if you update or delete a row, removing it from an >>> index, the data will stay in that index until vacuum comes along. >>> >>> Also, there's no point in doing a REINDEX after a VACUUM FULL; >>> vacuum full rebuilds all the indexes for you. >

Re: [GENERAL] Help with slow table update

> > [skipped] > > > > This is where using sets becomes really tedious, as Postgres severely > lacks an upsert-like statement. > > I don't think there are joins allowed in UPDATE statement, so I will need > to use WITH query, right? > > Also, I'm not sure how LEFT JOIN will help me isolate and inser

Re: [GENERAL] Help with slow table update

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pawel Veselov Sent: Tuesday, April 14, 2015 8:01 PM To: Jim Nasby Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Help with slow table update [skipped] This is where using sets becomes

Re: [GENERAL] Help with slow table update

On Tue, Apr 14, 2015 at 3:29 PM, Jim Nasby wrote: > On 4/14/15 4:44 PM, Pawel Veselov wrote: > >> On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby > > wrote: >> >> On 4/14/15 1:28 PM, Pawel Veselov wrote: >> >> >> I wonder if what I need to do, considering t

Re: [GENERAL] Help with slow table update

On 4/14/15 4:44 PM, Pawel Veselov wrote: On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote: On 4/14/15 1:28 PM, Pawel Veselov wrote: I wonder if what I need to do, considering that I update a lot of "the same" rows as I process this

Re: [GENERAL] Help with slow table update

On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby wrote: > On 4/14/15 1:28 PM, Pawel Veselov wrote: > >> >> I wonder if what I need to do, considering that I update a lot of "the >> same" rows as I process this queue, is to create a temp table, update >> the rows there, and then update the actual tables

Re: [GENERAL] Help with slow table update

On 4/14/15 1:28 PM, Pawel Veselov wrote: I wonder if what I need to do, considering that I update a lot of "the same" rows as I process this queue, is to create a temp table, update the rows there, and then update the actual tables once at the end... That's what I'd do. The other option would

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 7:37 PM, Jim Nasby wrote: > On 4/13/15 7:01 PM, Pawel Veselov wrote: > >> Cursors tend to make things slow. Avoid them if you can. >> >> >> Is there an alternative to iterating over a number of rows, where a >> direct update query is not an option? >> >> I really doubt

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 6:03 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov > wrote: > >> >> r_agrio_hourly - "good", r_agrio_total - "bad". >> >> Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual >> time=2.248..

Re: [GENERAL] Help with slow table update

On 4/13/15 7:01 PM, Pawel Veselov wrote: Cursors tend to make things slow. Avoid them if you can. Is there an alternative to iterating over a number of rows, where a direct update query is not an option? I really doubt that either the actual processing logic, including use of types has any

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov wrote: > > r_agrio_hourly - "good", r_agrio_total - "bad". > > Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual > time=2.248..2.248 rows=0 loops=1) >-> Index Scan using u_r_agrio_hourly on r_agrio_hourly > (cost=0.42..9

Re: [GENERAL] Help with slow table update

On Sun, Apr 12, 2015 at 5:40 PM, Jim Nasby wrote: > On 4/9/15 6:18 PM, Pawel Veselov wrote: > >> Hi. >> >> I have a plpgsql procedure that updates a few similar tables. >> for some reason, updates on one of the tables take a lot longer the >> updates on the other ones. The difference is, say, 7 s

Re: [GENERAL] Help with slow table update

On 4/9/15 6:18 PM, Pawel Veselov wrote: Hi. I have a plpgsql procedure that updates a few similar tables. for some reason, updates on one of the tables take a lot longer the updates on the other ones. The difference is, say, 7 seconds vs. 80 milliseconds. the procedure uses cursors and record v

  1   2   3   4   5   6   7   8   9   10   >