Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

services which don't need PostgreSQL (e.g. SSH or X11 login or a web- or mail server) shouldn't depend on it. One of the purported advantages of systemd over SystemV init is that it starts up services in parallel, so a service which takes a long (or infinite) time to start doesn't block other servic

Re: [GENERAL] Client Authentication methods

actly it make the difference for > client > if i use md5/password  in pg_hba.conf file in DB server?. See https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PASSWORD With method password, passwords are sent in plain text. With md5, an md5 hash of the password, the username

Re: [GENERAL] EXPLAIN command just hangs...

es, which isn't the case for PostgreSQL.) hp -- _ | Peter J. Holzer| we build much bigger, better disasters now |_|_) || because we have much more sophisticated | | | h...@hjp.at | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <ht

[GENERAL] Query plan for Merge Semi Join

understand: There are 6 non-null distinct values of arbeitsvolumen in facttable_kon_eh, and each appears 36958 times. 36958 * 5 + 1 = 184791. So it stops once it reaches the largest value. Although now I'm wondering how it knows that this is the largest value without scanning to the end).

Re: [GENERAL] "Shared strings"-style table

lity POV I think a dictionary lookup in Perl is a lot nicer than 50 joins (or 300 in your case). hp -- _ | Peter J. Holzer| we build much bigger, better disasters now |_|_) || because we have much more sophisticated | | | h...@hjp.at | management tools. __/

Re: [GENERAL] pglogical bidirectional replication of sequences

On 2017-09-01 10:29:51 +0200, Peter J. Holzer wrote: > pglogical supports replication of sequences, and although the way it > does this suggests that it can't really work in both directions > (actually I'm sceptical that it works reliably in one direction), of > course I had to try i

Re: [GENERAL] pglogical bidirectional replication of sequences

On 2017-09-01 09:57:52 -0600, Rob Sargent wrote: > On 09/01/2017 02:29 AM, Peter J. Holzer wrote: > >TLDR: Don't. > > > >I'm currently conducting tests which should eventually lead to a 2 node > >cluster with working bidirectional logical replication. > > > >

[GENERAL] pglogical bidirectional replication of sequences

ing the whole database. hp -- _ | Peter J. Holzer| we build much bigger, better disasters now |_|_) || because we have much more sophisticated | | | h...@hjp.at | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.ed

Re: [GENERAL] Porting libpq to QNX 4.25

On 2017-08-22 12:57:15 -0300, marcelo wrote: > We'll replace those QNX machines with WIndows XP ones The future is already here — it's just not very evenly distributed. SCNR, hp -- _ | Peter J. Holzer| we build much bigger, better disasters

[GENERAL] Postgresql_for_odoo

Service Postgresql_for_odoo not found and Server internal error while open localhost:8069. If I uninstall postgresql, Error stopping and delete postgresql_for_odoo. Thanks

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On 2017-08-18 15:57:39 -0500, Justin Pryzby wrote: > On Fri, Aug 18, 2017 at 10:47:37PM +0200, Peter J. Holzer wrote: > > On 2017-08-18 06:37:15 -0500, Justin Pryzby wrote: > > > On Fri, Aug 18, 2017 at 01:01:45PM +0200, Rob Audenaerde wrote: > > > > Can anyone

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

tion. So apparently columnname open-parenthesis tablename closed-parenthesis is a specific syntactic construct, but I can't find it documented anywhere. hp -- _ | Peter J. Holzer| we build much bigger, better disasters now |_|_) || because we have much mo

Re: [GENERAL] Dealing with ordered hierarchies

ber). That way two transactions won't be able to add a node with the same sequence number under the same parent. You will have to handle duplicate key errors, though. hp -- _ | Peter J. Holzer| we build much bigger, better disasters now |_|_) || because we have much

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

gt; No problem. The plan is to perform 2k rows at once, which is not much. Are rows deleted from tablea after they are migrated? Otherwise you will have a problem: select ... limit 2000 offset 1234000 will have to retrieve 1236000 rows and then discard 1234000 of them. hp -- _ | Pet

Re: [GENERAL] storing large files in database - performance

like a long time, and the time seems to grow exponentially with > file size rather than linearly. > > > > Do these numbers surprise you? Yes. on my system, storing a 25 MB bytea value takes well under 1 second. hp -- _ | Peter J. Hol

Re: [GENERAL] Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future?

On 2017-05-05 11:46:55 -0700, John R Pierce wrote: > On 5/5/2017 11:28 AM, Peter J. Holzer wrote: > > On 2017-05-04 23:08:25 +0200, Sven R. Kunze wrote: > > On 03.05.2017 12:57, Thomas Güttler wrote: > > Am 02.05.2017 um 05:

Re: [GENERAL] Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future?

resolve a bottleneck, then by all means separate them. hp [1] "I read somewhere on the internet" is usually not a good reason. -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy. | | | h...@hjp.at

Re: [GENERAL] Handling psql lost connections

erver, start a screen session and psql in the screen session. Then if your network connection drops you can simply login again and resume the screen session. Of course this only works if you have a shell login on the server which may not be the case. hp -- _ | P

Re: [GENERAL] inevitability of to_date() when converting representations which don't represent whole timestamps

might be useful in practice, but whichever of them you pick, you've picked the wrong one with a probability of 2/3. “The first monday in the year -1 of the proleptic Gregorian calendar” would be consistent with how to_timestamp('12:34:56', 'HH24:MI:SS') works, but apart from that and being in

Re: [GENERAL] Postgres Permissions Article

onstraint you need to be the owner of the referencing table and have the references privilege on the referenced table. It's not symmetrical.) hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy. | | | h...@hjp.at

Re: [GENERAL] Autoanalyze oddity

On 2017-03-05 12:01:07 +0100, Peter J. Holzer wrote: [...] > At the current rate of inserts, this threshold will be reached on > March 24nd. I'll check whether the table is analyzed then. It was (a little earlier than expected because pg_class.reltuples didn't increase in the me

Re: [GENERAL] Autoanalyze oddity

On 2017-03-05 08:39:05 -0800, Adrian Klaver wrote: > On 03/05/2017 03:01 AM, Peter J. Holzer wrote: > >So it is likely that something happened on that day (disk full?) which > >wiped out the contents of pg_stat_user_tables. > > Are there any logs from that time, either Postg

Re: [GENERAL] Autoanalyze oddity

On 2017-03-03 06:39:35 -0800, Adrian Klaver wrote: > On 03/03/2017 12:33 AM, Peter J. Holzer wrote: > >This is with PostgreSQL 9.5.6 on Debian Linux. > > > >I noticed that according to pg_stat_user_tables autoanalyze has never > >run on a lot of tables. Here is one exa

[GENERAL] Autoanalyze oddity

urrent: I see entries in most_common_vals which were only inserted in January. Is it possible that autoanalyze runs without updating pg_stat_user_tables? hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy.

Re: [GENERAL] Loose indexscan and partial indexes

On 2017-02-10 14:24:36 +0100, Thomas Kellerer wrote: > Peter J. Holzer schrieb am 10.02.2017 um 14:02: > > So it's doing a sequential scan on the initial select in the recursive > > CTE, but using the index on the subsequent selects. > > > > But why? If it uses the

[GENERAL] Loose indexscan and partial indexes

Heap Fetches: 2 Planning time: 8.883 ms Execution time: 0.801 ms (23 rows) 800 times faster :-). hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy. | | | h...@hjp.at

[GENERAL] Streaming replication protocol

Hello, I'm trying to write a program that speaks the streaming replication protocol (for logical decoding). I get to the part where I issue a query: START_REPLICATION SLOT regression_slot LOGICAL 0/0; And after that, I receive an empty copy_both_response then a copy_data that has a "Primary

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

tend to cache plain-text passwords to resubmit them for each transaction, but to use something more ethereal, like session cookies or kerberos tickets. hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy. | | | h

Re: [GENERAL] COPY: row is too big

filled. Keeping all this in mind, the limit is between 250 | and 1600. hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy. | | | h...@hjp.at | -- John Gordon __/ | http://www.hjp.at/ |http://downlode.o

Re: [GENERAL] explain analyze showed improved results without changes, why?

ally low (off, sz) value which matches the query. So now the query can return after checking only a handful of rows. LIMIT, EXISTS, etc. are awful when you want predictable performance. You may be lucky and the rows you are looking for are just at the start or you may be unlucky and you have to s

Re: [GENERAL] Is is safe to use SPI in multiple threads?

out if necessary: Currently my database and server process run on the same machine, but I could distribute them over several machines with (almost) no change in logic. hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || th

Re: [GENERAL] Index size

just for the changed field) will have to be updated. You can set fillfactor to a smaller value to make this less likely. hp -- _ | Peter J. Holzer| A coding theorist is someone who doesn't |_|_) || think Alice is crazy. | | | h...@hjp.at | -- John G

Re: [GENERAL] pg_dump fundenental question

Thank you all for the information. On 07/05/2016 10:10 AM, J. Cassidy wrote: > Hello Adrian, > > appreciate the prompt reply, thank you. > > As stated in the original email, I want to know whether compression > (whatever level) is on by default (or not) - if I supply N

Re: [GENERAL] pg_dump fundenental question

can browse the backup file with less/heat/cat/tail etc. Regards, Henk On Tue, Jul 5, 2016 at 4:54 PM, J. Cassidy <s...@jdcassidy.eu> wrote: > I have hopefully an "easy" question. > If I issue the pg_dump command with no switches or options i.e. > /usr/local/pgsql/bin/pg_dump

Re: [GENERAL] pg_dump fundenental question

Rob, appreciate the reply but I have never used nor never will use "that" os (small capitals intentional. Regards, Henk

Re: [GENERAL] pg_dump fundenental question

Hello David, a good point, but I would prefer NOT to open a 324GB backup file in a text editor. I can however cat/less/head/tail the file in Linux. Regards, Henk  

Re: [GENERAL] pg_dump fundenental question

and perhaps have a different world view on how to explain things... TIA and regards, Henk. On 07/05/2016 07:54 AM, J. Cassidy wrote: > Hello all, > > I have hopefully an "easy" question. > > If I issue the pg_dump command with no switches or options i.e. > > /usr/lo

[GENERAL] pg_dump fundenental question

Hello all, I have hopefully an "easy" question. If I issue the pg_dump command with no switches or options i.e. /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd Is their any "default" compression involved or not? Does pg_dump talk to zlib during the dump process given that I

Re: [GENERAL] Ascii Elephant for text based protocols

\(/ o) ( o) ) | > | \_ (_ ) \ ) / | > | \ /\_/\)_/| > | \/ //| |\\ | > | v | | v | > |\__/| > || > | PostgreSQL 1996-2016 | > | 20 Years of success | > +----+ Nice. hp -- _ | Peter J. Holzer

Re: [GENERAL] Using both ident and password in pg_hba.conf

bedded mod_php) under different uids. So while running everything as nobody is the default, it is possible to use different users, and I would strongly recommend doing this if you have multiple customers. hp -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) |

Re: [GENERAL] Allow disabling folding of unquoted identifiers to lowercase

etablewithareallylongname less readable than SomeTableWithAReallyLongName. 2) Since case doesn't matter, they might be inconsistent: One programmer might write MyTable, another mytable, the third MYTABLE, ... 3) You might want to use a tool to automatically generate SQL queries, but th

Re: [GENERAL] Is it possible to recover the schema from the raw files?

Hello Adrian, > Then I am of no further use to this conversation. No problem at all. Thank you for your well considered input and ideas. Have a lovely day. Kindest regards, Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Distributed Table Partitioning

SSD and a larger hard disk. That might be a reason to look for an alternate hoster, but if he's otherwise happy, switching to an unknown provider might be considered too large a risk. hp -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) |

Re: [GENERAL] Unable to match same value in field.

the index helped in my case. Still, I find it worrying if a value which obviously is in the table can't be found using the index. hp -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) || suspenders; instead, I want to buy pants | | |

Re: [GENERAL] index problems (again)

On 2016-03-12 21:00:04 +, Geoff Winkless wrote: > On 12 March 2016 at 18:43, Peter J. Holzer <hjp-pg...@hjp.at> wrote: > > The question is what can be done to improve the situation. > > > > Tom thinks that correlation statistics would help. That seems plausible >

Re: [GENERAL] index problems (again)

On 2016-03-08 10:16:57 +, Geoff Winkless wrote: > On 7 March 2016 at 20:40, Peter J. Holzer <hjp-pg...@hjp.at> wrote: > > As Tom wrote, the estimate of having to read only about 140 rows is only > > valid if sc_id and sc_date are uncorrelated. In reality your query has &

Re: [GENERAL] index problems (again)

at knowledge to the planner. (And yes, I know that quite often the programmer is wrong - but I do believe in giving people enough rope to hang themselves with) hp -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) || suspenders; instead, I

Re: [GENERAL] BRIN Usage

On 2016-02-18 13:37:37 -0500, Tom Smith wrote: > it is for reducing index size as the table become huge.  > sorry for confusion, by timestamp, I meant a time series number, not the sql > timestamp type. > I need the unique on the column to ensure no duplicate,   but the btree index > is getting >

Re: [GENERAL] strange sql behavior

of records per block in your second query (65512/7372 = 8.9). I suspect that the records in your larger table are sorted by taxiid within each interval. You can almost certainly get a similar speedup by sorting each 5 minute interval by taxi id before appending it to the table. If querying by ta

Re: [GENERAL] Connecting to SQL Server from Windows using FDW

> On 23 January 2016 at 04:40, John J. Turner <fenwayri...@gmail.com> wrote: > On Jan 22, 2016, at 1:05 PM, ivo silvestre <ivo...@gmail.com> wrote: > > > I need to create a linked server between 2 Windows servers. In one I've > > PostgreSQL with admin privileges

Re: [GENERAL] Connecting to SQL Server from Windows using FDW

On Jan 22, 2016, at 1:05 PM, ivo silvestre wrote: > I need to create a linked server between 2 Windows servers. In one I've > PostgreSQL with admin privileges and in the other MS SQL with only read > access. > > I need to create a view (or a foreign table?) in PostgreSQL

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-03 10:02:18 -0500, Tom Lane wrote: > "Peter J. Holzer" <hjp-pg...@hjp.at> writes: > > Can those signals be safely ignored? Just blocking them (so that they > > are delivered after the UDF finishes) might be safer. But even that may > > be a probl

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-02 19:07:55 -0600, Jim Nasby wrote: > On 12/2/15 9:26 AM, Peter J. Holzer wrote: > >As explained in backend/utils/misc/timeout.c, the timers are never > >cancelled: If a timeout is cancelled, postgres just sees that it has > >nothing to do and resumes whatever

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 20:55:02 +0100, Peter J. Holzer wrote: > On 2015-12-01 18:58:31 +0100, Peter J. Holzer wrote: > > I suspect such an interaction because I cannot reproduce the problem > > outside of a stored procedure. A standalone Perl script doing the same > > requests

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 13:13:27 -0500, Tom Lane wrote: > "Peter J. Holzer" <hjp-pg...@hjp.at> writes: > > Postgres worker processes are single-threaded, are they? Is there > > something else which could interact badly with a moderately complex > > multithreaded I/O

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 07:16:04 -0800, Adrian Klaver wrote: > On 12/01/2015 06:51 AM, Peter J. Holzer wrote: > >A rather weird observation from the log files of our server (9.5 beta1): > > > >2015-12-01 09:23:37 CET [26265]: [42-1] user=fragner,db=wds,pid=26265 > >WARNING:

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 10:20:09 -0800, Adrian Klaver wrote: > On 12/01/2015 09:58 AM, Peter J. Holzer wrote: > >On 2015-12-01 07:16:04 -0800, Adrian Klaver wrote: > >>On 12/01/2015 06:51 AM, Peter J. Holzer wrote: > >>>A rather weird observation from the log files of our serve

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 18:58:31 +0100, Peter J. Holzer wrote: > I suspect such an interaction because I cannot reproduce the problem > outside of a stored procedure. A standalone Perl script doing the same > requests doesn't get a timeout. > > I guess Alvaro is right: I should strace the

[GENERAL] plperlu stored procedure seems to freeze for a minute

an IPC framework: See http://zeromq.org/ We use it to make RPC calls from stored procedures to a server process. -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) || suspenders; instead, I want to buy pants | | | h...@hjp.at | that

Re: [GENERAL] DISTINCT in STRING_AGG

On Nov 28, 2015, at 1:35 PM, Sterpu Victor wrote: > Hello > > Can I make a distinct STRING_AGG? > This is my query : > SELECT atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY > aqjs1.to_left) AS children > FROM administration.ad_query_join_select atjs > JOIN

Re: [GENERAL] How can I change defined schema of linked tables when using Access linked table manager odbc connection

On Oct 29, 2015, at 6:14 AM, Killian Driscoll wrote: > I am using postgresql 9.3 on Windows 8 64, and am using Access as a frontend > data entry. In postgresql I have changed the schema from 'public' to 'irll'. > The linked table in Access are still linked as 'public'

[GENERAL] Upgrade from postgresql-9.5alpha1 to postgresql-9.5alpha2 (Debian packages)

and restore yesterdays backup? * Something else? hp -- _ | Peter J. Holzer| I want to forget all about both belts and |_|_) || suspenders; instead, I want to buy pants | | | h...@hjp.at | that actually fit. __/ | http://www.hjp.at/ | -- http

Re: [GENERAL] Upgrade from postgresql-9.5alpha1 to postgresql-9.5alpha2 (Debian packages)

On 2015-08-18 20:40:10 +0900, Masahiko Sawada wrote: On Tue, Aug 18, 2015 at 6:06 PM, Peter J. Holzer hjp-pg...@hjp.at wrote: It looks like the catalog version has changed between 9.5alpha1 and 9.5alpha2: [...] So, what's the best way to do the upgrade? * Copy the bindir before

Re: [GENERAL] Queries for unused/useless indexes

pg_indexes AS idx ON indexrelname = indexname and idstat.schemaname = idx.schemaname JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname and idstat.schemaname = tabstat.schemaname (for some reason that makes it a lot slower, though) hp -- _ | Peter J. Holzer

Re: [GENERAL] Queries for unused/useless indexes

ON (idx.indexrelid =  i.indexrelid )  WHERE i.idx_scan 200    AND NOT idx.indisprimary    AND NOT idx.indisunique  ORDER BY 1, 2, 3; is not the query you posted in your original message. Here is what you posted: On Mon, May 25, 2015 at 10:41 AM, Peter J. Holzer hjp-pg...@hjp.at wrote

Re: [GENERAL] quick q re execute scope of new

On April 2, 2015, Scott Ribe wrote: On Apr 2, 2015, at 10:14 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: EXECUTE 'insert into ' || quote_ident(tblname) || ' values(' || new.* || ')' Not that easy, strings are not quoted correctly, and null values are blank. Might be a function to

Re: [GENERAL] window function ordering not working as expected

On Tue, Feb 17, 2015 at 4:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: I'm interested in seeing: * the date for the most recent result * test name (identifier) * most recent result (decimal value) * the worst (lowest decimal value) test result from

[GENERAL] window function ordering not working as expected

Greetings, I have a postgresql-9.3.x database with a table with a variety of date stamped test results, some of which are stored in json format (natively in the database). I'm attempting to use some window functions to pull out specific data from the test results over a a time window, but part of

Re: [GENERAL] Whats is lock type transactionid?

only when the other transaction terminates and releases its locks. I believe that describes what you're seeing -- Douglas J Hunley (doug.hun...@gmail.com)

Re: [GENERAL] Whats is lock type transactionid?

identify the tuple. Have you tried the lock monitoring queries on http://wiki.postgresql.org/wiki/Lock_Monitoring yet by chance? -- Douglas J Hunley (doug.hun...@gmail.com)

[GENERAL] pros/cons of using synchronous commit=off - AWS in particular

postgresql at Amazon? Has anyone done any benchmarking of this change on AWS? Since EBS is a black box to us as end users, I have no clue what type of caching- volatile or not-- may be going on behind the scenes. -- Larry J. Prikockis System Administrator 240-965-4597 (direct) lprikoc...@vecna.com

[GENERAL] Monitoring Streaming Replication in 9.2

Newb question here. I have streaming replication working with 9.2 and I'm using Bucardo's check_postgres.pl to monitor replication. I see that it runs this query on the slave: SELECT pg_last_xlog_receive_location() AS receive, pg_last_xlog_replay_location() AS replay That returns hex, which is

[GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

Hello, I'm working on an upgrade to our database cluster, attempting to move from 8.4 to 9.2. I'm encountering the following error when I attempt the upgrade (in pg_upgrade_restore.log): CREATE FUNCTION st_envelope_in(cstring) RETURNS st_envelope LANGUAGE c IMMUTABLE STRICT AS

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

The --link argument doesn't work, either: bash-4.1$ export LD_LIBRARY_PATH=/usr/pgsql-9.2/lib bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/data --new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/bin --new-bindir=/usr/pgsql-9.2/bin --check Performing Consistency

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

Adrian, On 1/1/14, 12:26 PM, Adrian Klaver adrian.kla...@gmail.com wrote: On 01/01/2014 09:08 AM, Reiser, John J. wrote: The --link argument doesn't work, either: Consult the last few lines of pg_upgrade_restore.log for the probable cause of the failure. Failure, exiting bash-4.1$ tail

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

On 1/1/14, 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Reiser, John J. rei...@rowan.edu writes: I'm working on an upgrade to our database cluster, attempting to move from 8.4 to 9.2. I'm encountering the following error when I attempt the upgrade (in pg_upgrade_restore.log): CREATE FUNCTION

Re: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

On 1/1/14, 3:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: Reiser, John J. rei...@rowan.edu writes: On 1/1/14, 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: What this smells like is a bug in the pg_dump --binary_upgrade logic that tries to preserve type OIDs from the old installation to the new

[GENERAL] Errors regarding non-existent files in pg_subtrans

G'day list. I've recently upgraded a number of servers from PostgreSQL 9.2.5 to 9.3.1 and have started getting the following errors every couple of hours along with some failed transactions. I have been unable to track down any sort of rhyme or reason for the errors yet, so I figured I'd check

Re: [GENERAL] partitioned table + postgres_FDW not working in 9.3

shigeru.han...@gmail.com wrote: Hi Lonni, 2013/9/25 Lonni J Friedman netll...@gmail.com: The problem that I'm experiencing is if I attempt to perform an INSERT on the foreign nppsmoke table on cluster a, it fails claiming that the table partition which should hold the data in the INSERT does

Re: [GENERAL] partitioned table + postgres_FDW not working in 9.3

On Thu, Sep 26, 2013 at 8:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: Thanks for your reply. This sounds like a relatively simple workaround, so I'll give it a try. Is the search_path of the remote session that postgres_fdw forces considered

[GENERAL] pg_basebackup: ERROR: could not find any WAL files (9.3)

Greetings, I've recently pushed a new postgres-9.3 (Linux-x86_64/RHEL6) cluster into production, with one master, and two hot standby streaming replication slaves. Everything seems to be working ok, however roughly half of my pg_basebackup attempts are failing at the very end with the error:

[GENERAL] postgres FDW doesn't support sequences?

I've got two 9.3 clusters, with a postgres foreign data wrapper (FDW) setup to point from one cluster to the other. One of the (foreign) tables associated with the foreign server has a bigint sequence for its primary key, defined as: id | bigint | not null

Re: [GENERAL] postgres FDW doesn't support sequences?

On Wed, Sep 25, 2013 at 2:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: If I INSERT a new row into the local table (not the foreign table version), without specifying the 'id' column explicitly, it automatically is assigned the nextval in the sequence

[GENERAL] partitioned table + postgres_FDW not working in 9.3

Greetings, I've got two different 9.3 clusters setup, a b (on Linux if that matters). On cluster b, I have a table (nppsmoke) that is partitioned by date (month), which uses a function which is called by a trigger to manage INSERTS (exactly as documented in the official documentation for

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

On Wed, Sep 18, 2013 at 2:02 AM, Kevin Grittner kgri...@ymail.com wrote: Lonni J Friedman netll...@gmail.com wrote: top shows over 90% of the load is in sys space. vmstat output seems to suggest that its CPU bound (or bouncing back forth): Can you run `perf top` during an episode and see

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

On Wed, Sep 18, 2013 at 2:02 AM, Kevin Grittner kgri...@ymail.com wrote: Lonni J Friedman netll...@gmail.com wrote: top shows over 90% of the load is in sys space. vmstat output seems to suggest that its CPU bound (or bouncing back forth): Can you run `perf top` during an episode and see

[GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

Greetings, I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant performance degradation. PostgreSQL simply feels slower. Nothing other than

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

On Tue, Sep 17, 2013 at 9:54 AM, Eduardo Morras emorr...@yahoo.es wrote: On Tue, 17 Sep 2013 09:19:29 -0700 Lonni J Friedman netll...@gmail.com wrote: Greetings, I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming replication hot standby slaves) on RHEL6-x86_64. Yesterday I

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

Thanks for your reply. Comments/answers inline below On Tue, Sep 17, 2013 at 11:28 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Tue, Sep 17, 2013 at 11:22 AM, Lonni J Friedman netll...@gmail.com wrote: c) What does logs say? The postgres server logs look perfectly normal, minus

Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation

On Tue, Sep 17, 2013 at 3:47 PM, Andres Freund and...@2ndquadrant.com wrote: Hi, On 2013-09-17 09:19:29 -0700, Lonni J Friedman wrote: I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded from 9.2.4

Re: [GENERAL] WAL Replication Working but Not Working

The first thing to do is look at your server logs around the time when it stopped working. On Wed, Aug 21, 2013 at 7:08 AM, Joseph Marlin jmar...@saucontech.com wrote: We're having an issue with our warm standby server. About 9:30 last night, it stopped applying changes it received in WAL

Re: [GENERAL] Streaming Replication Randomly Locking Up

I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the

Re: [GENERAL] Streaming Replication Randomly Locking Up

again so if it is a network issue, the replication is just stopping after some hiccup instead of retrying and resuming when things are back up. Thanks! On Thu, Aug 15, 2013 at 11:32 AM, Lonni J Friedman netll...@gmail.com wrote: I've never seen this happen. Looks like you might be using

Re: [GENERAL] Streaming Replication Randomly Locking Up

= -1 #log_checkpoints = off #log_connections = off #log_disconnections = off #log_error_verbosity = default I'm going to have a look at the NICs to make sure there's no issue there. Thanks again for your help! On Thu, Aug 15, 2013 at 11:51 AM, Lonni J Friedman netll...@gmail.com wrote

[GENERAL] postgres FDW cost estimation options unrecognized in 9.3-beta1

Greetings, I have a postgresql-9.3-beta1 cluster setup (from the yum.postgresql.org RPMs), where I'm experimenting with the postgres FDW extension. The documentation ( http://www.postgresql.org/docs/9.3/static/postgres-fdw.html ) references three Cost Estimation Options which can be set for a

Re: [GENERAL] postgres FDW cost estimation options unrecognized in 9.3-beta1

On Fri, Jul 26, 2013 at 3:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ; ERROR: option use_remote_estimate not found Am I doing something wrong, or is this a bug

[GENERAL] upgrading from 9.3-beta1 to 9.3-beta2 requires dump reload?

Greetings, I just got around to upgrading from 9.3-beta1 to 9.3-beta2, and was surprised to see that the server was refusing to start. In the log, I'm seeing: 2013-07-24 13:41:47 PDT [7083]: [1-1] db=,user= FATAL: database files are incompatible with server 2013-07-24 13:41:47 PDT [7083]: [2-1]

Re: [GENERAL] upgrading from 9.3-beta1 to 9.3-beta2 requires dump reload?

On Wed, Jul 24, 2013 at 2:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Lonni J Friedman escribió: I'm using the RPMs from yum.postgresql.org on RHEL6. Is this expected, intentional behavior? Do I really need to dump reload to upgrade between

Re: [GENERAL] Standby stopped working after PANIC: WAL contains references to invalid pages

Looks like some kind of data corruption. Question is whether it came from the master, or was created by the standby. If you re-seed the standby with a full (base) backup, does the problem go away? On Sat, Jun 22, 2013 at 12:43 PM, Dan Kogan d...@iqtell.com wrote: Hello, Today our standby

Re: [GENERAL] Standby stopped working after PANIC: WAL contains references to invalid pages

and has been working for about 2 hours. The file in the error message was an index. We rebuilt it just in case. Is there any way to debug the issue at this point? -Original Message- From: Lonni J Friedman [mailto:netll...@gmail.com] Sent: Saturday, June 22, 2013 4:11 PM To: Dan

  1   2   3   4   5   6   7   8   >