Perhaps one NOTIFY in five minutes - or longer.
>
> Yes, it’s a hack.
Or crib some code from
http://initd.org/psycopg/docs/advanced.html#async-notify or
https://godoc.org/github.com/lib/pq/listen_example , which is probably
less effort than assembling this collection of hacks and trying to
mak
#x27;. And since you are already using
wal-e for log shipping, you get full PITR available.
pg_dump for a logical backup is also a possibility, although with 13TB
you probably don't want to hold a transaction open that long and are
better off with wal-e, barman or other binary backup too
ix it. Probably by knowing which
files have changed and only resyncing them,for example using timestamps
from the database or storing 'incoming' files in a separate area from your
'archive'. Once you have this sorted you can do your backups every few
minutes and reduce your potential data loss.
--
Stuart Bishop
http://www.stuartbishop.net/
ceptable, perhaps the ability
to turn them off on an existing database is easily doable (a one way
operation).
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
nd/or the
startup scripts. If it doesn't work, the problem is elsewhere (eg.
some required path like /var/run or /tmp or /var/log/postgresql
doesn't exist or has peculiar permissions). If nothing else, you
should get more informative output rather than systemd hiding it away
somewhere.
--
ry, pushing changes to
disk on site).
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
stuck in session mode and up to 750
connections for the time being)
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
e that, but I suppose that's a topic for another day.
Yeah... integration with configuration management is going to be
interesting when PG allows you to modify config from the SQL command
line...
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
nel. And try different schedulers if
you haven't already - IIRC switching to deadline resolved one of our
load problems.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ur recovery.conf file, but the WAL file had not yet been
shipped and the output logged. I imagine streaming replication happily
reconnected soon after. This is all quite normal.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresq
ackages with Ubuntu.
PostgreSQL 9.1 is available with a few clicks using the software
center with Ubuntu 12.04 (precise) and Ubuntu 13.04 (raring). If you
want PostgreSQL 9.2 or 9.3, you are currently best off installing
Ubuntu 12.04 (precise) and following the instructions at
https://wiki.postgresq
On Thu, Aug 29, 2013 at 2:44 PM, Tatsuo Ishii wrote:
> I have a question about hot_standby_feedback parameter. In my
> understanding, if this parameter is on, a long running transaction on
> standby will not be canceled even if the transaction conflicts.
> As you can see vacuum on the primary rem
rk after doing this is probably obvious from your
log files.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ble.com/Ubuntu-12-04-3-2-Kernel-Bad-for-PostgreSQL-Performance-td5735284.html
tl;dr for that thread seems to be a driver problem (fusionIO?), I'm
unsure if Ubuntu specific or in the upstream kernel.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
did not attempt to pull down WAL files
that had already been shipped and were available locally. This would
save resources when you need them most - the hot standbys have fallen
behind.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general
hen it isn't, such as when you are connecting to a remote
server). You shouldn't need to invoke anything from /usr/lib directly,
unless you are doing something particularly low level.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@p
On Thu, Feb 14, 2013 at 7:21 AM, Sergey Konoplev wrote:
> On Wed, Feb 13, 2013 at 12:53 AM, Stuart Bishop
> wrote:
>> I'm unable to offload my backups to one of my PG 9.1 hot standbys
>> using purely streaming replication. After a few hours, usually on the
>>
---
hot_standby | on
hot_standby_feedback | on
max_standby_archive_delay| 3
max_standby_streaming_delay | 3
wal_receiver_status_interval | 10
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgre
y make pg_dump backups from a hot standby without
the hot standby lagging far behind the master.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
now have the PostgreSQL startup
script and syslog fighting over ownership of this directory. The
Debian and Ubuntu packages pipe PostgreSQL's stderr here, and are not
configured to use syslog nor PostgreSQL's builtin log rotation.
--
Stuart Bishop
http://www.stuartbishop.net/
--
S
s. I think for warnings you
would need to write a helper in PL/pgSQL or similar and use it to log
the warnings using plpy.prepare/plpy.execute.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ike you are triggering
some sort of race condition in the buildout generated .py files. You
might be able to confirm and/or work around the issue by getting your
own stanza added to the top of the generated site.py, explicitly
importing the problematic modules right at the top before any buildout
m
oo,
steering clear of .0 releases in favor of waiting for the initial
patch release.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ork. I believe the problem
is being fixed, by letting the history files be shipped along with the
WAL files.
http://archives.postgresql.org/pgsql-general/2011-12/msg00456.php
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
T
dby_feedback as an alternative.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ue to
conflict with recovery
DETAIL: User was holding a relation lock for too long.
Can anyone shed some insight? My understanding of hot_standby_feedback
is that it should make this sort of query cancellation never happen.
On Tue, Aug 14, 2012 at 6:34 PM, Stuart Bishop wrote:
> Hi.
>
>
ing in a few minutes. I'm successfully using pg_dump on other
hot standbys that take half a day to dump with tables active enough
that they certainly should have triggered autovacuums.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-genera
standby of ServerB, start it up.
Does this look correct to people?
Am I going to end up in trouble copying files into pg_xlog like this on a
busy system?
Is it overengineered? eg. will a master ensure everything is streamed to
connected hot standbys before a graceful shutdown?
--
aused by different
versions of libssl, but I don't think that is the case here unless one
of the packages got statically linked with an old libssl. I haven't
had time to investigate so I've disabled SSL for now, even though
replication appears to work apart from the disconnections.
--
ke miliseconds or something
> that would be helpful.
On the hot standby:
SELECT now()-pg_last_xact_replay_timestamp() AS lag;
This gives you the lag time as a PostgreSQL interval.
(It also might give you a value if you run it on a database that is
not a hot standby if it started in recover
inly like to see this or similar (encode the querys into a
single line of ascii, lossy is ok). I like my logs both readable and
greppable.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, May 4, 2012 at 4:09 PM, Stuart Bishop wrote:
> On Fri, Apr 27, 2012 at 4:42 PM, Frank Lanitz wrote:
>> Hi folks,
>>
>> Just looking for a nice server side solution to implement some
>> fundamental logic for an application. plpythonu looks in this tmers ver
that
job. There are counter examples too, where the Python code is much
cleaner and in some cases lets you do things impossible in plpgSQL.
Access to the Python standard library gives you a great toolkit, and
it being 'unsafe' you can do stuff you would otherwise need to write
and de
ansaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.
You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space
On Thu, Feb 23, 2012 at 2:58 AM, Greg Williamson
wrote:
> Stuart Bishop shaped the aether to ask:
>
>> Hi.
>>
>> I need to measure how far in the past a hot standby is, async
>> streaming replication.
>
> Not sure if this will help, but we are using repmgr
&
replay and the lag time will increase).
Is there some way to get this same information on the master?
pg_stat_replication contains the log information, but I can't see how
to map this to a timestamp.
Is there a better way of measuring this?
--
Stuart Bishop
http://www.stuartbishop.net/
--
On Mon, Jan 23, 2012 at 9:37 PM, Simon Riggs wrote:
> On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop
> wrote:
>> On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards
>> wrote:
>>> Is synchronous postgresql replication slower than asynchronous? If so, how
>>&g
sits as a proxy between
the client and the databases, and as queries are executed
simultaneously, a synchronous replication setup should be just as fast
as an unreplicated setup.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
On Sat, Nov 5, 2011 at 1:26 AM, Tom Lane wrote:
> Stuart Bishop writes:
>> We also found this problem did not occur on one of our staging
>> systems, which had a default statistics target of 100. Lowering the
>> statistics on the relavant columns from 1000 to 100 and re
een able to organize getting tools on a
suitable server yet.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
nections from my webapp which
I know can safely go through pgbouncer in transaction pooling mode.
Or would there be some way of detecting if the current session has
access to stuff that persists across transactions and this feature
could be added to the existing connection pools?
--
Stuart Bishop
ECC
memory or on your disk platter.
So if you are worried about such things, give up now because your
application is going to have to run with hardware and software in this
universe with all its limitations.
Maybe I should start a business in providing UUID collision insurance?
--
S
ine readable format. Perhaps useful filters might start to exist and eventually end up in contrib or core.
--
Stuart Bishop
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
On Wed, Oct 7, 2009 at 3:09 AM, Alvaro Herrera
wrote:
> Stuart Bishop wrote:
>
>> >I don't think the committed patch touches anything involved in what
>> >you're testing, but if you could grab CVS tip from the 8.4 branch (or
>> >the snapshot from ftp.po
On Tue, Oct 6, 2009 at 8:28 PM, Alvaro Herrera
wrote:
> Stuart Bishop wrote:
>>
>>
>> On Mon, Oct 5, 2009 at 11:00 PM, Alvaro Herrera
>> wrote:
>> >Stuart Bishop wrote:
>> >>On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
>> >> wrote:
On Mon, Oct 5, 2009 at 11:00 PM, Alvaro Herrera
wrote:
Stuart Bishop wrote:
On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
wrote:
> A similar issue was discussed just recently here:
> http://archives.postgresql.org/pgsql-general/2009-09/msg01219.php
>
> That issue invo
On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
wrote:
> On 5 Oct 2009, at 8:58, Stuart Bishop wrote:
>
>> I'm running our products test suite against PostgreSQL 8.4.1. The test
>> suite runs fine against 8.3.7.
>>
>> With 8.4.1, some of our tests are failing
el, and
the database will have been recreated a few instants ago using
'createdb --template test_template_db'.
One of the statement logs is at http://paste.ubuntu.com/285983/ - I
can't see anything unusual
going on but it might help diagnose the problem.
--
Stuart Bishop
http://w
Hi.
Is anyone aware of a connection pool or load balancer for PostgreSQL
that supports ident based authentication? Neither pgpool-ii nor
pgbouncer support this according to their docs, so I was wondering
what else is out there.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql
Hi.
Is anyone aware of a connection pool or load balancer for PostgreSQL
that supports ident based authentication? Neither pgpool-ii nor
pgbouncer support this according to their docs, so I was wondering
what else is out there.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql
stgresql releases always get a new package. This allows you to
have multiple major versions installed and running simultaneously.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://w
r help.
You can search the archives for the last time this was brought up.
Apparently, it isn't an easy fix. People hoped to have it addressed
for 8.4 but I don't know if it made it. Unfortunately, this makes GIN
indexes unusable for many applications such as text searching using
arbitrary
On Tue, Mar 31, 2009 at 11:10 AM, Tom Lane wrote:
> Stuart Bishop writes:
>> On Tue, Mar 31, 2009 at 8:59 AM, Tom Lane wrote:
>>> What's the actual size of that relation now? Is it growing rapidly?
>>> (I'm trying to figure out whether those writes *shoul
On Tue, Mar 31, 2009 at 8:59 AM, Tom Lane wrote:
Stuart Bishop writes:
I just had a brief outage on a production server running 8.3.6, which
I suspect was triggered by me running a table bloat report making lots
of pgstattuple calls.
The first I got of it was the script I'd just kicke
essions and some manual mappings to map LETTER
THORN -> th, LETTER LATERAL CLICK -> X etc. Just mapping CAPITAL
LETTER XX -> XX and SMALL LETTER XX -> xx seems to get you most of
europe if you special case SHARP S -> ss and THORN -> th.
>>> s = u'ァ'
>>>
gstattuple as it was
the only unusual activity happening at that time and as far as I'm
aware we have no hardware alerts and the box has been running smoothly
for quite some time.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postg
On Tue, Mar 3, 2009 at 11:53 AM, Sanjay Arora wrote:
> Is it possible to host postgreSQL on Amazon's cloud? What are the issues
> involved?
Runs just fine under Ubuntu. We haven't tried it under serious load though.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent v
On Mon, Oct 13, 2008 at 5:05 PM, Stuart Bishop <[EMAIL PROTECTED]> wrote:
> So what was the final recommended process for building a stand alone
> database from a pg_dump of a replicated node?
> So if I'm reading this thread correctly, the alternative is 'DROP _sl
> C
uot;_sl".alterTableRestore( $1 )"
PL/pgSQL function "uninstallnode" line 14 at PERFORM
Failed to exec uninstallNode() for node 1
So if I'm reading this thread correctly, the alternative is 'DROP _sl
CASCADE;', which doesn't do a full cleanup. Is there no
on declaring it impossib=
le.
plpythonu is unrestricted, so if you have the ability to create plpythonu=
stored procedures you effectively have full filesystem access on your
database server as the user your database is running as. So don't put
open('/etc/passwd','w')
col)=md5('...')
Depending on your app, you might want to round your timestamps to avoid
getting two identical messages in the system only a millisecond apart.
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
p2.
(It would be wonderful if pg_restore supported this natively, but I don't
know enough to know if it is possible or not.)
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
k, but I
> was wondering if there is now a true conversion from postgresql arrays
> to python and vice-versa.
With 8.2, no there is not. Beyond the simple number and string times
everything gets cast to a string (arrays, tsvectors, etc.). I have no idea
if anyone is working on this for
t run a beta on production, I'd
recommend working with 8.3. If nothing else, the docs are s much nicer
now (big thanks to whoever did this btw! I can't wait to be able to upgrade
and wish I had time to do the preliminary migration right now.).
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
working or failing depending on the existence
of an index seems rather wrong to me.)
Only relevant discussion on this I can find is
http://archives.postgresql.org/pgsql-hackers/2007-01/msg01581.php. There
appear to be no replies visible though :-(
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.
s making the system more complex, thus creating more things that could
go wrong.
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
bug report at
https://launchpad.net/distros/ubuntu/+source/postgresql-8.1/+bugs might be
more appropriate.
--
Stuart Bishop <[EMAIL PROTECTED]> http://www.canonical.com/
Canonical Ltd.http://www.ubuntu.com/
signature.asc
Description: OpenPGP digital signature
s ids and kill any outstanding ones.
To work around he second issue, we attempt to drop a number of times with a
short sleep between each try. Which is rather 'icky.
I have similar issues I need to deal with on our staging server, which each
day automatically needs to have the database reset
for Malone is on our wiki at
https://wiki.launchpad.canonical.com/MaloneEmailInterfaceUserDoc
We use it internally the way you describe.
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
ring is case insensitive, and the C locale ordering is case
sensitive because it is simply comparing the ASCII codes.
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
ACE FUNCTION sha1(text) RETURNS char(40) AS '
import sha
return sha.new(args[0]).hexdigest()
' LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT;
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
NDEX name on table(param1,(param2 = 'true'));
> but it's not working.
CREATE UNIQUE INDEX foo ON table(param1, (NULLIF(param2, false)))
- --
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.
f you need Java on the boxes running PostgreSQL, that will probably be
the decisive factor - Java licencing makes it difficult for free
distributions to provide easy installation and support.
--
Stuart Bishop <[EMAIL PROTECTED]> http://www.canonical.com/
Ca
unwarrented - current consensus
seems to be to transform line endings in the string to the
one-true-format expected by Python's guts:
http://mail.python.org/pipermail/python-dev/2005-January/051214.html
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbish
Martijn van Oosterhout wrote:
On Wed, Jan 19, 2005 at 06:28:25PM +1100, Stuart Bishop wrote:
Michael Fuhr wrote:
If Python's behavior is intentional then the newline burden would
seem to be on the user or on plpythonu. I think Tom's point is
that that's just silly
Changing t
ces.
It is too late for the Python 2.3 series anyway - 2.3.5 is being
released Jan 26th and there won't be a 2.3.6. If it was championed and
it decided that the above example is a bug and not a feature and a patch
produced, it could get into 2.4.1 due April and 2.5+
I suspect this means fi
to it, wouldn't it?
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Tom Lane wrote:
Stuart Bishop <[EMAIL PROTECTED]> writes:
Marc G. Fournier wrote:
| A current list of *known* supported platforms can be found at:
| http://developer.postgresql.org/supported-platforms.html
I notice that Ubuntu is not yet on this list. I can confirm that
PostgreSQL 7.
u
5.4 (hoary) on all platforms. Version freeze for hoary is today, so that
version is fixed and the 8 series won't be officially supported until
the following release (October 2005), although installing the Debian
packages should work just fine.
- --
Stuart Bishop <[EMAIL PROTE
s that it isn't using the traditional
definition.
People are used to months being ambiguous so it is less likely to cause
upsets, although it still bites people because their toolkits definition
of 'month' does not match their business rules of 'month' (which might
be 30 days, 31 d
und up to the nearest existant
time, you then have the issue that '2am April 3rd + 1 day == 3am Aril
3rd + 1 day'.
- --
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFBge+sAfqZj7rGN0oRAgInAJsEuY
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Stuart Bishop wrote:
| create or replace function BatchBalanceStatus(int, int, int) returns
| varchar as '
| ~balance, needed, freestock = args
| ~if balance < 0:
| ~return "Unhandled"
| ~elif freestock >= need
>= needed:
~return "OK"
~else:
~return "Ordered"
' language plpythonu;
Works just fine here on 7.4.5
- --
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFBdfmqAfq
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tom Lane wrote:
| Stuart Bishop <[EMAIL PROTECTED]> writes:
|
|>How much overhead is there in storing a timestamp with timezone as
|>opposed to one without?
|
|
| Exactly zero. You have a misconception about what the datatype really
|
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tom Lane wrote:
| Stuart Bishop <[EMAIL PROTECTED]> writes:
|
|>I'm trying to determine the best way of saying 'The current time in UTC
|>with no time zone information'.
|
|
| Isn't that a contradiction in terms?
Not a
IME ZONE which appears to
work. However, PostgreSQL parses this into the much more confusing
"timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone)"
which is what is appearing on my generated documentation.
Is there any magic string like 'NOW'::tim
86 matches
Mail list logo
