Re: [GENERAL] pg_restore load data

[bricklen]

On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson  wrote:

> v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.)
>
> During a "whole database" restore using pg_restore of a custom dump, when
> is the data actually loaded?  I've looked in the list output and don't see
> any "load" statements.
>

Look for COPY lines, that's how the data is restored.

Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.

[bricklen]

On Mon, Apr 24, 2017 at 9:17 PM, Andres Freund  wrote:

>
> Questions (answer as many you can comfortably answer):
> - How many MB/s, segments/s do you see on busier servers?
> - What generates the bulk of WAL on your servers (9.5+ can use
>   pg_xlogdump --stats to compute that)?
> - Are you seeing WAL writes being a bottleneck?OA
> - What kind of backup methods are you using and is the WAL volume a
>   problem?
> - What kind of replication are you using and is the WAL volume a
>   problem?
> - What are your settings for wal_compression, max_wal_size (9.5+) /
>   checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?
> - Could you quickly describe your workload?
>

* Postgresql 9.3
* 1500+ db servers
* Daily peak for busy databases: 75 WALs switched per second (less than 10%
of the servers experience this)
* Avg per db: 2 WALs/s
* Mainly generated by large batch sync processes that occur throughout the
day, and by a legacy archiving process to purge older data (potentially
many millions of cascading deletes).
*Half the servers have (encrypted) pg_dump backups, WAL volume hasn't
proved to be a problem there, though dump size is a problem for a few of
the larger databases (less than 1TB).
* Inter-data-centre replication is all streaming, across DC's (over the
WAN) WAL shipping is over compressed SSH tunnels.
Occasionally the streaming replication falls behind, but more commonly it
is the cross-DC log shipping that becomes a problem. Some of the servers
will generate 50+ GBs of WAL in a matter of minutes and that backs up
immediately on the masters. Occasionally this has a knock-on effect for
other servers and slows down their log shipping due to network saturation.
* checkpoint_segments: 64, checkpoint_timeout: 5 mins, wal_buffers: 16MB

Workload:
70% of servers are generally quiet, with occasional bursty reads and writes.
20% are medium use, avg a few hundred transactions/second
10% average around 5k txns/s, with bursts up to 25k txns/s for several
minutes.
All servers have about 80% reads / 20% writes, though those numbers flip
during big sync jobs and when the purging maintenance kicks off.

Re: [GENERAL] Re-sync slave server

[bricklen]

On Mon, Jun 6, 2016 at 5:32 PM, Patrick B  wrote:

> My set up:
>
>
> Master --> slave01 (streaming replication) --> slave02 (streaming
> replication)
> Master --> slave03 (wal_files 4 days old, not streaming replication)
>
> The wal_files are stored into each server.
> But when the slave03 was down, the wal_files weren't being copied into it.
> We took too long to discover that, and now we lost some wal_files.. they've
> been recycled...
>
> So my question is:
>
> Can I just do a RE-SYNC from slave01 to slave03 of the data folder?
> Or do I have to sync all the database again?
>
>
If the master is successfully shipping WALs to slave03 now, re-syncing that
replica from slave01 (or slave02) should work fine. By "re-sync" I assume
you mean a full filesystem copy using pg_basebackup etc.

Re: [GENERAL] More correlated (?) index woes

[bricklen]

On Tue, Mar 29, 2016 at 3:47 AM, Geoff Winkless  wrote:

> On 28 March 2016 at 20:23, I wrote:
>
>> Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's
>> absolutely not reasonable to expect this to be an optimal strategy.
>> ​
>>
> It occurred to me that even though the majority of values are NULL, there
> are ​
>
> ​1691 unique values in pa.field1, so I suppose it might seem more
> attractive to the planner than it should do (that's more unique values than
> there are scdate entries).
>
>
Perhaps a partial index like "create index pa_sc_id_pidx on pa (sc_id) with
(fillfactor=100) where field1 IS NULL;" will help?

Re: [GENERAL] vacuum - reclaiming disk space.

[bricklen]

On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell 
wrote:

> I have a large table with numerous indexes which has approximately doubled
> in size after adding a column - every row was rewritten and 50% of the
> tuples are dead.  I'd like to reclaim this space, but VACUUM FULL cannot
> seem to finish within the scheduled downtime.
>
> Any suggestions for reclaiming the space without excessive downtime?
>

pg_repack is a good tool for removing bloat.
https://github.com/reorg/pg_repack

Re: [GENERAL] Subtract one array from another, both with non-unique elements

[bricklen]

On Sun, Mar 6, 2016 at 9:22 AM, Alexander Farber  wrote:

> could someone please recommend the most efficient way subtracting elements
> of one array from the other in PostgreSQL 9.5?
>

There are quite a few examples of array functions at
http://postgres.cz/wiki/Array_based_functions, probably worth having a
look.

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Sat, Jan 9, 2016 at 2:35 PM, drum.lu...@gmail.com 
wrote:

> rsync -azr --progress --partial postgres@$MASTER_IP:
>> /var/lib/postgresql/data/var/lib/postgresql/data/ --exclude
>> postmaster.pid
>
>
> Ah ok! So this will do an incrementa, right? not supposed to copy ALL the
> base/ again?
>

Yes, this is for incremental copying from the upstream source.
Actually, you don't need the -r with -a (it is implied), and you can run it
first with --dry-run to see what it _would_ do.
If you are not shipping over the WAN, then omit the -z flag as you do not
need compression.

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Sat, Jan 9, 2016 at 2:22 PM, drum.lu...@gmail.com 
wrote:

> *rsync would be something like:*
>
> from slave1:
> rsync -av data/pg_xlog slave2:/var/lib/postgresql/data/
>

Normally I run something like the following from the slave I am setting up.

rsync -azr --progress --partial postgres@$MASTER_IP:/var/lib/postgresql/data
/var/lib/postgresql/data/ --exclude postmaster.pid


> I didn't unterstand why doing RSYNC twice... sorry
>

Unless the source db cluster you are rsync'ing from is stopped, there will
be changes to data files replicated from the master. The second rsync might
not be necessary given the WALs are shipping from the master to slave2.

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Sat, Jan 9, 2016 at 2:10 PM, drum.lu...@gmail.com 
wrote:

> I could stop the slave then But I'm afraid getting it back online and
> get some other errors
>

At this point I think your options are slim. If you are feeling
adventurous, you can try doing the rsync with the slave running, then do a
second rsync with the slave stopped or do it from the master after putting
the master in backup mode (eg. executing "pg_start_backup('slave_backup')")

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Sat, Jan 9, 2016 at 1:54 PM, drum.lu...@gmail.com 
wrote:

> Hi,
>
> If you are able to stop Postgres on the slave you are taking the base
>> backup from, you could do this:
>
>
> I'm not... the data base is 2 TB.
> So, a RSYNC would take DAYS.  And I'm not able to stop the SLAVE for
> that long time
>

Depending on when you took the base backup and how many changes have
occurred at your source (slave1) database cluster, the rsync execution time
may or may not take as long as a new base backup if is only only shipping
deltas (changed files).

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Sat, Jan 9, 2016 at 1:49 PM, drum.lu...@gmail.com 
wrote:

> Hi,
>
> If the master is successfully ships WALs to the slave you are setting up
>> you do not need the "stream" option.
>
>
> yes.. the master is successfully shipping the WALs
>
> Is there anything else? Help, please hehehehe
>

If you are able to stop Postgres on the slave you are taking the base
backup from, you could do this:

1). Stop postgres on slave1
2). Rsync slave1 to slave2 to copy only the deltas.
3). When you start up slave2 the WALs that the master has shipped to slave2
should apply and bring your system up to consistency.

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Sat, Jan 9, 2016 at 12:36 PM, drum.lu...@gmail.com 
wrote:

> Do you have any other ideia? Do you know if --xlog it's the problem and I
> should re-run the pg_basebackup again with the *--xlog-method=stream*
> option?
>


If the master is successfully ships WALs to the slave you are setting up
you do not need the "stream" option.

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

On Fri, Jan 8, 2016 at 8:44 PM, drum.lu...@gmail.com 
wrote:

Hi, I'm a bit too lazy to try suss out the exact reasons for your failure,
but here is a reasonably thorough guide to set up replication:
http://dba.stackexchange.com/a/53546/24393

A few tips:
- Having the master ship WALs to the slaves is handy if you can pull it
off. If you are doing it over the wire and using rsync, "-z" for
compression is recommended. If you are doing the tar format of the
pg_basebackup, you *must* have the master ship the WALs to the slave
otherwise it won't be able to synchronize (the "stream" method ships WALs
over the wire so the end result is a synchronized system.

- I always run pg_basebackup from the slave I am building, for simplicity.
- I create new slaves almost every day (we have thousands of databases)
using a bash script and it almost much never fails. In essence it is a big
wrapper around the pg_basebackup command (though we are using pg93 mostly).

The base backup command that I run from the slave I am building:
pg_basebackup --pgdata=$PGDATA --host=$MASTER_IP --port=$PGPORT
--username=replication --no-password --xlog-method=stream --format=plain
--progress --verbose

The recovery.conf:
standby_mode = 'on'
primary_conninfo = 'user=replication host=$IP_OF_UPSTREAM_SLAVE_OR_MASTER
port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
recovery_target_timeline = 'latest'
archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup
/path/to/WALs %r'
restore_command = 'cp /path/to/WALs/%f "%p" 2>>
/your/PGDATA/path/pg_log/standby.log'

Re: [GENERAL] Submitting to this list

[bricklen]

On Wed, Jan 6, 2016 at 9:15 AM, Melvin Davidson 
wrote:

>
> I would like to see the following added to the introduction to this list.
>
> "When submitting to this list, please include the full version of
> PostgreSQL and the O/S you are using.
> Also, if you are reportaing a problem, it is essential that a minimal
> amount of schema & data be
> provided in order to duplicate problem being reported, as well as exact
> error verbage encountered and
> any relevant extracts from the postgresql log file(s)."
>

There are already wiki entries for the "proper" way to submit reports or
ask for help. A link to those might be sufficient.
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
https://wiki.postgresql.org/wiki/SlowQueryQuestions

Re: [GENERAL] PostgreSQL GUI tools

[bricklen]

On Sun, Jan 3, 2016 at 2:15 AM, subhan alimy  wrote:

> Is there any PostgreSQL GUI tools to pull the tables structure along with
> the relationships?
>

Along with the other suggestions, I have personally used
http://www.dbschema.com/. It works well, but I don't recall if there was a
free version.

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

On Thu, Oct 22, 2015 at 9:15 AM, Tom Lane  wrote:

> bricklen  writes:
> > What would have happened to the WAL-shipping-only standby if the WALs
> were
> > all applied? Would it have it balked at applying a WAL containing bad
> data
> > from the master, or would it have applied the WAL and continued on? For
> the
> > latter, would physical corruption on the master even transfer via WAL?
>
> Hard to tell.  I'd have guessed that corruption that made a page
> unreadable would not transfer across WAL (streaming or otherwise), because
> the master could not have read it in to apply an update to it.  However,
> we don't know the exact sequence of events here; there may have more than
> one step on the way to disaster.
>
> regards, tom lane
>

I would have liked to have had the opportunity to answer those questions
myself but alas, in the heat of the moment some of the data useful for
forensics was lost.

Thanks again!

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

A follow-up question if I may,


> bricklen  writes:
>> > Yes, it is definitely a table. There was originally an index on that
>> table
>> > which threw the original error (about sibling mismatch). I dropped the
>> > index and attempted to recreate it, which failed. Further investigation
>> led
>> > to discovery of corruption in the table.
>>
>

There are several hot standby servers attached to the master, some
streaming, and one in a different data centre that is using WAL shipping
only.
The streaming slave IIRC got the corruption from the master (I can't check
now, it was rebuilt).
What would have happened to the WAL-shipping-only standby if the WALs were
all applied? Would it have it balked at applying a WAL containing bad data
from the master, or would it have applied the WAL and continued on? For the
latter, would physical corruption on the master even transfer via WAL?

I didn't get a chance to answer those questions because we promoted the DR
WAL-shipping standby before it got to the corrupted section.

Thanks,

Bricklen

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

On Wed, Oct 21, 2015 at 12:52 PM, Tom Lane  wrote:

> bricklen  writes:
> > Yes, it is definitely a table. There was originally an index on that
> table
> > which threw the original error (about sibling mismatch). I dropped the
> > index and attempted to recreate it, which failed. Further investigation
> led
> > to discovery of corruption in the table.
>
> Hm.  There's still something weird about this though.  Maybe there is no
> data at all between pages 1226710 and 690651?  Might be worth doing some
> poking around with contrib/pageinspect/.
>
>
Ah, good idea.

Thanks again!

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

Hi Tom,

On Wed, Oct 21, 2015 at 11:46 AM, Tom Lane  wrote:

> bricklen  writes:
> > I get the following output for ctid, id, other_id, tstamp:
>
> >  (690651,42) |318698967 |   347978007 | 2015-10-20 01:55:41.757+00
> >  (690651,43) |318698968 |   347978008 | 2015-10-20 01:55:41.663+00
> >  (690651,44) |318698969 |   347978009 | 2015-10-20 01:55:42.005+00
> > ERROR:  invalid page in block 1226710 of relation base/16750/27244
>
> > It appears 690652 is what would be dd'd if that's the route we take. Is
> > that accurate?
>
> I'm confused by the block mentioned in the error message not having
> anything to do with the TID sequence.  I wonder whether it refers to an
> index not the table proper.  What query were you using to get this output,
> exactly?  Have you confirmed which relation has relfilenode 27244?
>

Yes, it is definitely a table. There was originally an index on that table
which threw the original error (about sibling mismatch). I dropped the
index and attempted to recreate it, which failed. Further investigation led
to discovery of corruption in the table.


>
> > Because the message indicates the corruption is in the table's page, not
> > the page header, according to the docs zero_damaged_pages probably won't
> > work.
>
> I should think that zero_damaged_pages would work fine, if the problem
> is indeed in the base table.
>

I will make note of that.



>
> Another thing to keep in mind here, if you've got replication slaves,
> is that I'm not sure whether the effects of zero_damaged_pages would
> propagate to slaves.  Have you investigated the possibility that some
> slave has an uncorrupt copy that you could dd into place in the master?
>

We do have one uncorrupted slave, and one corrupted. I have a 4 hour
delayed WAL-apply script that runs on the primary slaves in the disaster
recovery data centres, and I stopped that process as soon as I saw the
error about the sibling mismatch on the master. It is a viable candidate to
fail over to, if we can swing a 20+ hour window of data loss. Right now
that is an undesirable option.


>
> > Is this the correct command if option #2 is chosen? Can it be executed
> > against a running cluster?
> > dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1
> > conv=notrunc
>
> Uh, no, you're not accounting for the fact that such an offset wouldn't be
> in the first segment file of the relation.
>

Hmm, I wasn't sure about that. Thanks for confirming that.

As it stands, my next step is going to be a pg_dump of one of the
up-to-date slaves (with corruption) but I will exclude the bad table. Given
that I know the PK id range, I can COPY out the table's contents before and
after the affected data. This way we can at least recover from backup if
things get entirely borked.

The next part of the plan is to create a temporary version of the table
with all data other than the corrupted range, then do some transaction-fu
to rename the tables.


Thank you for your response, and any other insights are gratefully received.


Cheers,

Bricklen

[GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

Hi,

We have run into some corruption in one of our production tables. We know
the cause (a compute node was moved), but now we need to fix the data. We
have backups, but at this point they are nearly a day old, so recovering
from them is a last-resort and will incur significant downtime.
We are running 9.3.9

Following the steps at
http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html

I get the following output for ctid, id, other_id, tstamp:

 (690651,42) |318698967 |   347978007 | 2015-10-20 01:55:41.757+00
 (690651,43) |318698968 |   347978008 | 2015-10-20 01:55:41.663+00
 (690651,44) |318698969 |   347978009 | 2015-10-20 01:55:42.005+00
ERROR:  invalid page in block 1226710 of relation base/16750/27244

It appears 690652 is what would be dd'd if that's the route we take. Is
that accurate?


Because the message indicates the corruption is in the table's page, not
the page header, according to the docs zero_damaged_pages probably won't
work.

What are my options?

1). Enable zero_damaged_pages and execute VACUUM FREEZE (and hope).
2). dd the block(s) using the output of the ctid query above.

It is multi-gigabyte table that is extremely heavily used (100's to 1000's
of queries per second) so a VACUUM FULL or CLUSTER are options we'd really
like to avoid if possible. The database is about 250GB, not huge, but big
enough that slaves and backups are time consuming to redo, or recover from.

Will attempting zero_damaged_pages cause any harm as the first step (other
than the obvious destruction of any bad pages)?


Is this the correct command if option #2 is chosen? Can it be executed
against a running cluster?
dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1
conv=notrunc



Thanks,

Bricklen

[GENERAL] From: Bricklen Anderson

[Bricklen Anderson]

Hi


http://forum.myways.su/felt.php?drive=bhankyuytv3630es


brick...@gmail.com

Re: [GENERAL] Backups over slave instead master?

[bricklen]

On Thu, May 15, 2014 at 1:55 PM, Bruce Momjian  wrote:

> On Thu, May  1, 2014 at 12:39:44PM -0700, bricklen wrote:
> > Or alternatively, if "backup" = pg_dump, then backups can taken from the
> slave
> > too. Have a look at pg_xlog_replay_pause() + pg_dump +
> pg_xlog_replay_resume().
> > http://www.postgresql.org/docs/current/static/functions-admin.html#
> > FUNCTIONS-RECOVERY-CONTROL-TABLE
>
> Uh, what is the pause for?  So the transaction will not be cancelled?
>
>
Yes.

Re: [GENERAL] copy expensive local view to an RDS instance

[bricklen]

On Tue, May 6, 2014 at 8:07 AM, Marcus Engene  wrote:

> On 06/05/14 16:58, bricklen wrote:
>
>>
>> A very quick search shows that rds supports dblink, so perhaps that would
>> work.
>> http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/
>> CHAP_PostgreSQL.html
>>
>
> Then I'd need to open our servers to external visits. It would be lovely
> if dblink_exec could push a subselect of data instead instead of pull from
> RDS. Does this make sense?
>

Is the idea to pull data from the RDS to your local machine? If so, dblink
or possibly plproxy[1] should be able to do that.

[1] https://wiki.postgresql.org/wiki/PL/Proxy

Re: [GENERAL] copy expensive local view to an RDS instance

[bricklen]

On Tue, May 6, 2014 at 5:52 AM, Marcus Engene  wrote:

> Hi,
>
> I have a local db behind a firewall etc. Basically, I'd like to do what
> I'd locally would...
>
> create table abc
> as
> select
> *
> from
> local_expensive_view;
>
> abc - on RDS
> local_expensive_view - on local machine
>
> How would you go about doing this?
>
> Thanks,
> Marcus
>

A very quick search shows that rds supports dblink, so perhaps that would
work.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html

Re: [GENERAL] Backups over slave instead master?

[bricklen]

On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas wrote:

> On 05/01/2014 10:31 AM, Edson Richter wrote:
>
>  I'm wondering if would be possible to execute these backups in the slave
>> server instead, so I can avoid the overhead of backups on master system?
>>
>
> If you're on PostgreSQL 9.3, you can backup the slave server safely. If
> not, you'll need to run this command on the master system first:
>
> SELECT pg_start_backup('some-label');
>
> After the backup is done, run this on the master server:
>
> SELECT pg_stop_backup();
>

Or alternatively, if "backup" = pg_dump, then backups can taken from the
slave too. Have a look at pg_xlog_replay_pause() + pg_dump +
pg_xlog_replay_resume().
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE

Re: [GENERAL] importing a messy text file

[bricklen]

On Wed, Apr 30, 2014 at 1:07 AM, Willy-Bas Loos  wrote:

>
> Hi,
>
> I have a 56GB textfile that i want to import into postgres.
> The file is tab delimited and not quoted.
>

Would Pgloader be an option? http://tapoueh.org/pgloader/

Re: [GENERAL] Log file monitoring and event notification

[bricklen]

On Sat, Apr 5, 2014 at 8:47 AM, Andy Colson  wrote:

> Hi All.
>
> I've started using replication, and I'd like to monitor my logs for any
> errors or problems.  I don't want to do it manually, and I'm not interested
> in stats (a la PgBadger).
>
> What I'd like, is the instant PG logs: "FATAL: wal segment already
> removed" (or some such bad thing), I'd like to get an email.
>
> 1st: is anyone using a program that does something like this?  What do you
> use?  How do you like it?
>

Tail 'n' Mail from Bucardo might be what you're after:
http://bucardo.org/wiki/Tail_n_mail

Re: [GENERAL] Playing with 9.4devel - unnest

[bricklen]

On Mon, Mar 10, 2014 at 10:16 AM, Tim Kane  wrote:

> But what I really wanted to do, is unnest multiple sets of array values as
> returned from a table/query..
>

Craig Ringer posted an interesting answer to a somewhat related question a
few months ago on Stack Overflow: http://stackoverflow.com/a/17646605

Re: [GENERAL] high throughput 9.3, master/slave question

[bricklen]

On Fri, Feb 28, 2014 at 1:54 PM, Matthew Chambers wrote:

>
> Initially, I had my application servers using the slave for short, read
> only queries, but this turned out to be highly unstable. The slave would
> start refusing connections, and the logs would fill with:
>
> ERROR:  canceling statement due to conflict with recovery
>
> I've tried these 2 settings:
>
> max_standby_archive_delay = -1
> max_standby_streaming_delay = -1
>
> But then I starting getting these:
> DETAIL:  User transaction caused buffer deadlock with recovery.
>
> Read requests come in at anywhere between 200 and 1000/second.
>
> I was wondering if there is some combination of configuration settings
> that would safely let me use the slave for read only queries?
>

Have you tried setting max_standby_archive|streaming_delay to several
minutes (or whatever makes sense for your typical query durations), rather
than disabling those settings with -1?

Re: [GENERAL] pg_dump/pg_restore issues

[bricklen]

On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé wrote:

> Hi, I'm backing up a big database using the --exclude-table option for
> two tables, say table1 and table2. Then another backup of only those
> tables, so, the final result are three backup files.
>
> basic.backup
> table1.backup
> table2.backup
>
> The problem I'm facing is at the restore moment is that basic.backup
> contains view definitions related to table1 or table2, hence, the
> restore does not create those views.
>
> How do you recommend to workaround this?.
>
> P.S.: I create three files because table1 and table2 are tables with
> blob data, and we use basic.backup to create testing database where we
> don't need blob data.
>

The --section option of pg_dump might allow you dump the views separately.
Alternatively, if you know the names of the views that will fail, you could
pg_dump as you are doing now, but in custom format (-Fc), then use
pg_restore to create a list file from the contents, comment out the views,
pg_restore using the list file (minus those views), then pg_dump using
another list file with *only* those views.

Re: [GENERAL] Streaming Replication - Error on Standby

[bricklen]

On Tue, Feb 11, 2014 at 11:25 AM, bobJobS  wrote:

>
> To get the standby server to a point, I tool a globals dump and a data dump
> of the primary server and build the standby.
>
> Then I executed pg_startbackup, rsync data dir to standby data dir (to
> catch
> any changes made while I was building the standby) and finally
> pg_stopbackup... all on the primary.
>


The steps you are using will not work. You cannot use a pg_dump/pg_dump
backup from a master to set up a slave. pg_dump generates a "logical"
backup, which is used for recovery not setting up slaves.  A very
high-level view of the replication setup:
- put the master in backup mode
- pg_basebackup of the master to the slave (no slave data exists prior to
this step)
- take the master out of backup mode

Re: [GENERAL] Streaming Replication - Error on Standby

[bricklen]

On Tue, Feb 11, 2014 at 10:12 AM, bobJobS  wrote:

> Postgres 9.3.2.
> RHEL 5
>
> After performing all of the Streaming Replication setup steps,
>

What replication steps?


>   database system identifier differ between the primary and standby
>

How did you take the initial backup of the master? Did you rsync the master
filesystem (after issuing pg_start_backup()) or use pg_basebackup, or did
you literally take a pg_dump of the master and try to turn that backup into
a slave? If the latter, you will need to use the rsync/pg_basebackup method.

There are some reasonably thorough steps at
http://dba.stackexchange.com/a/53546/24393 if you want to compare them
against what you tried already.

Re: [GENERAL] Better Connection Statistics

[bricklen]

On Fri, Feb 7, 2014 at 2:24 PM, bricklen  wrote:

>
> On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas wrote:
>
>> s I said in the original message, pg_stat_statements only gives query
>> stats for the whole database. What I want to know, is information about
>> each client. Say there's a specific connection from 192.168.1.20. I want to
>> know:
>>
>> * How many queries that connection has executed.
>> * How much CPU time that connection has used since it connected.
>> * How much data was sent to that connection.
>> * How much data that connection sent to the database.
>>
>> And so on. I don't believe that's currently possible. Effectively, it
>> would just be adding a few more columns to pg_stat_activity to track
>> cumulative totals, since it always has the status of all connections.
>>
>

I don't know any tools off-hand, but you might be able to generate partial
statistics from the log files with a descriptive log_line_prefix like "%m
[%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] ". Using the %p
and vxi/txid might help to group the queries executed for easier
consumption. I don't think that helps much with individual connections
though.

Re: [GENERAL] Better Connection Statistics

[bricklen]

On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas wrote:

>
> > Perhaps this might be of use.
> >
> > http://www.postgresql.org/docs/current/static/pgstatstatements.html
>
> Nope. As I said in the original message, pg_stat_statements only gives
> query stats for the whole database. What I want to know, is information
> about each client. Say there's a specific connection from 192.168.1.20. I
> want to know:
>
> * How many queries that connection has executed.
> * How much CPU time that connection has used since it connected.
> * How much data was sent to that connection.
> * How much data that connection sent to the database.
>
> And so on. I don't believe that's currently possible. Effectively, it
> would just be adding a few more columns to pg_stat_activity to track
> cumulative totals, since it always has the status of all connections.
>
>
> __
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions
> related to this email
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Grep'ing for a string in all functions in a schema?

[bricklen]

On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver wrote:

> Since Postgres does not consider a table as a dependency of a function if
> that table is referenced in the function (probably a good reason), I often
> find myself in a position of asking "is this table/sequence/index
> referenced in any of these N number of functions?"
>
> Is there an easy way of essentially grep'ing all of the functions in a
> given schema for a string?
>

A method I've used in the past is to create a view of function source which
can then be searched.
Eg.

CREATE OR REPLACE VIEW function_def as
SELECT n.nspname AS schema_name,
   p.proname AS function_name,
   pg_get_function_arguments(p.oid) AS args,
   pg_get_functiondef(p.oid) AS func_def
FROM   (SELECT oid, * FROM pg_proc p WHERE NOT p.proisagg) p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~~ 'pg_%'
ANDn.nspname <> 'information_schema';

select * from function_def where func_def ilike '%foo%';

Re: [GENERAL] Filtering queries by IP

[bricklen]

On Mon, Jan 20, 2014 at 6:20 PM, Sergey Konoplev  wrote:

> On Mon, Jan 20, 2014 at 5:21 AM, Leonardo M. Ramé 
> wrote:
> > Hi, I'm trying to find the cause of slow performance on some screens of
> > an application. To do that, I would like to be able to log all the
> > queries made by an specific IP addres, is this possible?.
>
> I don't think it's possible with pure postgres. However, you can
> temporarily turn all statements logging by
>
> set log_min_duration_statement to 0;
>
> then collect enough logs and turn it back by
>
> set log_min_duration_statement to default;
>
> Also set log_line_prefix to '%t %p %u@%d from %h [vxid:%v txid:%x]
> [%i] ' in the config file, it will give you a lot of useful
> information including host data. And turn log_lock_waits on as it
> might be useful when your slow queries are waiting for something.
>
> And finally, this gotcha will flatten all the multi-line log records
> and filter them by a specified IP.
>
> DT='2013-11-21'
> SUB='192.168.1.12'
>
> rm tmp/filtered.log
> if [ ! -z $SUB ]; then
> cat /var/log/postgresql/postgresql-$DT.log | \
> perl -pe 's/(^\d{4}-\d{2}-\d{2} )/###$1/; s/\n/@@@/; s/###/\n/' | \
> grep -E "$SUB" | perl -pe 's/@@@/\n/g' >tmp/filtered.log
> fi
>
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray...@gmail.com
>
>
In addition to what Sergey has posted above, you could also run your logs
through PgBadger [1], using a log_line_prefix similar to what is suggested
by Sergey, and then filter by "--include-query" regex. I've never tried,
but glancing at PgBadger's docs it looks like it should work more or less.

[1] https://github.com/dalibo/pgbadger

Re: [GENERAL] Any freeware graphic display of DDL software available?

[bricklen]

On Thu, Jan 16, 2014 at 2:57 PM, Susan Cassidy <
susan.cass...@decisionsciencescorp.com> wrote:

> It doesn't appear that DBVisualizer does an ER type diagram, which is what
> I really need.
>
>
DBSchema outputs ER diagrams:
http://www.dbschema.com/database-er-diagrams.html

I think the paid version is about $300 IIRC.

Re: [GENERAL] How to delete completely duplicate rows

[bricklen]

On Wed, Jan 1, 2014 at 4:14 AM, Janek Sendrowski  wrote:

> I want to delete duplicates in my table. I've dropped the unique
> constraint to insert my data.
> My id value is a hash calculated witch the values of the two other columns.
> So I want to delete all columns, which are indentical, but keeping one.
>
> DELETE FROM table t1 USING table t2 WHERE t1.id = t2.id AND t1.ctid >
> t2.ctid
>
> But the oids aren't unique enough.
> What else could I do?


http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Delete_duplicate_rows_with_window_analytic_functions
http://wiki.postgresql.org/wiki/Deleting_duplicates

Re: [GENERAL] How to list current user and database in PSQL

[bricklen]

On Tue, Dec 31, 2013 at 10:55 AM, peterlen  wrote:

> After logging into the PSQL Shell Script as a particular user and
> database, I
> want to display who my current user is and what database I am in as I may
> have forgotten who I am logged in as in order to make sure any commands are
> going to the right place.  In Oracle's SQLPlus you can do something like
> "show user" and get feedback on the user you are logged in as.  Is there an
> equivalent thing for PSQL to list the user and database?
>
> Thanks - Peter
>

As Adrian Klaver mentioned, you can set your psql prompt. Here are few
entries from my $HOME/.psqlrc

\set PROMPT1 '%n@%`hostname`:%> [%/] %x%# '
\timing on
\pset pager off
\x auto
\set HISTCONTROL = ignoreboth

Re: [GENERAL] unexpected pageaddr error in db log

[bricklen]

On Thu, Dec 19, 2013 at 1:27 AM, wd  wrote:

>
> On Thu, Dec 19, 2013 at 2:24 PM, Amit Langote wrote:
>
>> "unexpected pageaddr" log entry in this case means the standby reached
>> the end of existing WAL.
>> So, just before connecting to walsender for streaming replication, it
>> logs this.
>>
>
>
> Thanks for your reply. So this means I can safely omit this message?
>

Yes, you can ignore that message. It message level was something more
severe than "[LOG]" then it would be worth investigating further.

Re: [GENERAL] Zero dead tuples, when significant apparent bloat

[bricklen]

On Tue, Dec 10, 2013 at 5:05 PM, John Melesky
wrote:

> It seems clear that there were dead tuples, since the table size shrank to
> an eighth of its previous size. Why did analyze not pick that up?
> Am I missing something?
> This is a very large database, so we want to introspect against live/dead
> tuple percentage to minimize the tables we run a VACUUM FULL against.
>


If you willing to install the pgstattuple[1] extension, what does the
output say? Note, there is some overhead on larger tables (disk I/O
primarily)

select * from pgstattuple('your table');

Also, check the output from bloat query at
https://wiki.postgresql.org/wiki/Show_database_bloat

[1]  http://www.postgresql.org/docs/current/static/pgstattuple.html

Re: [GENERAL] DB Audit

[bricklen]

On Tue, Dec 10, 2013 at 6:53 AM,  wrote:

>   I am using sybase ase as dbms and I would migrate to postgresql, but
> the absence of a built in DB audit functionality is a show stopper for me.
>
> So I would know if there is a way to get information about DB events like:
>
> server boots
>
> login & logout
>
> table access.
> attempt to access particular objects
>
>  particular user’s actions.
>

In addition to what Laurenz has mentioned,by default auditing does not
exist, but there are ways to add your own, some of which include:

- https://github.com/2ndQuadrant/audit-trigger
- Create views over your tables which also select a db function to log
SELECT access.
Eg.
create or replace view user_details as
select col1, col2, col3, col4, null as audit from some_table
union all select null, null, null, null, audit_function(current_user) as
func;

There are probably other options, but I can't think of any off the top of
my head at the moment. This question has come up in these lists before and
on Stack Overflow, so you might want to search around a bit for other
options.

Re: [GENERAL] Mismatched pg_class.reltuples between table and primary key

[bricklen]

Hi Alvaro,

Thanks for the link.

On Mon, Dec 2, 2013 at 4:35 PM, Alvaro Herrera wrote:

> bricklen escribió:
> > We recently experienced a hard crash of a dev server due to lack of
> > resources (we think, still investigating).
> > That resulted in an interesting scenario where one of the tables was
> > returning 12 rows from "select * from tbl", but "select * from tbl order
> by
> > " was only returning 11.
> > Looking at pg_class.reltuples, it could be clearly seen that the table in
> > question had a value of 12, but the primary key had only 11.
>
> This sounds very similar to what was reported in
>
> http://www.postgresql.org/message-id/20131126123244.gi23...@alap2.anarazel.de
>
> In short, you probably want to make sure to upgrade to this week's
> release.
>

If it is indeed the same issue (and I see something similar in an actual
production system running 9.2.5 which was recovered from a crashed LVM just
the other day), are the chances of data corruption particularly high? From
reading over that linked thread, it looked like the problem was likely to
stem from wraparound.

Once the patch is applied, will the existing data still be vulnerable? I
haven't been paying close attention to that particular thread, so I might
have missed a few things regarding the fixes -- will a full dump + reload
be necessary?

Thanks,

Bricklen

[GENERAL] Mismatched pg_class.reltuples between table and primary key

[bricklen]

We recently experienced a hard crash of a dev server due to lack of
resources (we think, still investigating).
That resulted in an interesting scenario where one of the tables was
returning 12 rows from "select * from tbl", but "select * from tbl order by
" was only returning 11.
Looking at pg_class.reltuples, it could be clearly seen that the table in
question had a value of 12, but the primary key had only 11.

My system catalog ignorance is probably showing, but I assumed that the
table and PK pg_class.reltuples values should always be the same?

The initial observations were made by a developer:
1). After an ANALYZE, PK reltuples remained at 11.
2). After REINDEX, PK reltuples was still 11.
3). After VACUUM FULL, PK reltuples was reset to 12.

The db was quiescent at this point (rows were not being added or removed).

I would chalk this up to a localized index corruption issue, but earlier
today I observed the same thing on a different table which did not
originally show a difference between the table and PK reltuples values.
This is following a VACUUM FREEZE, and an ANALYZE run, as well as at least
two restarts of the db cluster since the initial failure.

Can anyone suggest whether this is an index corruption issue, or maybe a
misunderstanding on my part about what the reltuples attribute means? Or
perhaps it is something else?


PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-54), 64-bit
default_statistics_target = 200


Here is a query I hacked together to raise a WARNING if the reltuples value
of a table was different than the associated primary key or unique key. I
visually ignored any values that were above the default_statistics_target
value, to factor out sample size issues.

DO
$$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT  n.nspname schemaname,
c.relname as tablename,
c.reltuples::NUMERIC as tbl_tuples,
i.relname as idx_name,
i.reltuples::NUMERIC as idx_tuples
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'::"char"
AND i.relkind = 'i'::"char"
AND n.nspname NOT IN ('pg_catalog','information_schema')
AND (x.indisunique IS TRUE OR x.indisprimary IS TRUE)
ORDER BY 1,2,4
LOOP
IF ( rec.idx_name IS NOT NULL
 AND rec.tbl_tuples IS DISTINCT FROM rec.idx_tuples ) THEN
RAISE WARNING 'Mismatched tuple counts - Table %, tuples: %,
unique key: %, tuples: %',
rec.schemaname||'.'||rec.tablename, rec.tbl_tuples,
rec.idx_name, rec.idx_tuples;
END IF;
END LOOP;
END
$$;

Re: [GENERAL] unnest on multi-dimensional arrays

[bricklen]

On Wed, Nov 27, 2013 at 11:28 PM, Pavel Stehule wrote:

> Hello
>
> postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
>  RETURNS SETOF anyarray
>  LANGUAGE plpgsql
> AS $function$
> DECLARE s $1%type;
> BEGIN
>   FOREACH s SLICE 1  IN ARRAY $1 LOOP
>   RETURN NEXT s;
>   END LOOP;
> RETURN;
> END;
> $function$;
> CREATE FUNCTION
>
> postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
>  reduce_dim
> 
>  {1,2}
>  {2,3}
> (2 rows)
>

Hi Pavel,

I hope you don't mind, I took the liberty of adding your nifty function to
the Postgresql Wiki at
https://wiki.postgresql.org/wiki/Unnest_multidimensional_array

Feel free to edit directly or suggest any changes to it.

Cheers,

Bricklen

Re: [GENERAL] nested query vs left join: query planner very confused

[bricklen]

On Wed, Nov 27, 2013 at 7:56 AM, David Rysdam  wrote:

>
>
> At my client's location, the query is very slow (same table size,
> similar hardware/config, although they are running 9.0.x and I'm on
> 9.2.x). By "slow" I mean like an *hour*. 'explain' has this structure:
>
> Seq scan on mags
> Filter:
> SubPlan 1
> Materialize
> Seq scan on sigs
>
> I'd never heard of Materialize before, so I looked into it. Seems to
> make a virtual table of the subquery so repetitions of the parent query
> don't have to re-do the work. Sounds like it should only help, right?
>
> The client's 'explain analyze' shows this:
>
>Seq Scan on mags  (cost=0.00..187700750.56  rows=47476  width=4)
> (actual time=3004851.889..3004851.889  rows=0  loops=1)
>   Filter:  ((signum IS NOT NULL) AND (NOT (SubPlan 1)))
>   SubPlan 1
> -> Materialize  (cost=0.00..3713.93  rows=95862  width=4)
> (actual time=0.011..16.145  rows=48139  loops=94951)
>-> Seq Scan on sigs (cost=0.00..2906.62
> rows=95862 width=4) (actual time=0.010..674.201  rows=95862  loops=1)
>Total runtime: 3004852.005 ms
>


Has the client ANALYZEd recently? What happens if the client issues the
following commands before executing the query?
VACUUM ANALYZE lp.sigs;
VACUUM ANALYZE lp.mags;

If that doesn't change the plan, could you post the values for
effective_cache_size, shared_buffers, random_page_cost, cpu_tuple_cost,
work_mem and how much RAM is in the client machine?

Re: [GENERAL] Postgres as In-Memory Database?

[bricklen]

On Mon, Nov 18, 2013 at 11:16 PM, Stefan Keller  wrote:

>
> > I don't think there's any evidence that the Postgres developers ignore
> > useful optimisations.  What you're arguing is that the optimisation
> > you have in mind isn't covered.
>
> No; my point is that I - and others like Stonebraker, Oracle and SAP etc.
> - see room for optimization because assumptions about HW changed. To me,
> that should be enough evidence to start thinking about enhancements.
>


You must not read the -hackers list often enough, there are regularly long
discussions about changing settings and adding features to take into
account new hardware capabilities.
If you feel so strongly that the core developers are not scratching your
itch, donate some code or money to fund they feature you feel are missing.

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

[bricklen]

On Mon, Nov 18, 2013 at 8:30 PM, Brian Wong  wrote:

>  I've tried any work_mem value from 1gb all the way up to 40gb, with no
> effect on the error.  I'd like to think of this problem as a server process
> memory (not the server's buffers) or client process memory issue, primarily
> because when we tested the error there was no other load whatsoever.
> Unfortunately,  the error doesn't say what kinda memory ran out.
>

I wasn't asking because I thought you should make it higher, I think you
should make it lower. Set it to 200MB and reload your conf files ("select
pg_reload_conf()") and try your queries again. work_mem is a per step
setting, used by aggregates and sort steps, potentially multiple times in a
single query, also multiplied by any other concurrent queries. In this
case, it might not be the cause, but certainly try a lower setting to rule
it out.

Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???

[bricklen]

On Mon, Nov 18, 2013 at 12:40 PM, Brian Wong  wrote:

> We'd like to seek out your expertise on postgresql regarding this error
> that we're getting in an analytical database.
>
> Some specs:
> proc: Intel Xeon X5650 @ 2.67Ghz dual procs 6-core, hyperthreading on.
> memory: 48GB
> OS: Oracle Enterprise Linux 6.3
> postgresql version: 9.1.9
> shared_buffers: 18GB
>
> After doing a lot of googling, I've tried setting FETCH_COUNT on psql
> AND/OR setting work_mem.  I'm just not able to work around this issue,
> unless if I take most of the MAX() functions out but just one.
>

What is your work_mem set to?
Did testing show that shared_buffers set to 18GB was effective? That seems
about 2 to 3 times beyond what you probably want.

Re: [GENERAL] pg_log filling up with false logs

[bricklen]

On Mon, Nov 11, 2013 at 6:32 PM, Shree  wrote:

> Also pg_ctl command to stop, restart does not seem to have any effect.
> Does anyone have any ideas?
>


I can't speak to what issued the shutdown command, but have you tried
"pg_ctl restart -m fast" to expedite the restart (if that is indeed what
you are after) ?

Re: [GENERAL] Full text search on partial URLs

[bricklen]

On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin
wrote:

> On 11/06/2013 01:47 PM, bricklen wrote:
>
>>
>> On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin
>> mailto:zev-pg...@strangersgate.com>> wrote:
>>
>> Hi,
>>
>> I have Postgres full text search set up for my application and it's
>> been working great!  However, my users would like their searches to
>> turn up parts of URLs.  For example, they would like a search for
>> "foobar" to turn up a document that contains the string
>> "http://example.com/foobar/__blah <http://example.com/foobar/blah>"
>> (and similarly for queries like "example" and "blah).  With the
>> default dictionaries for host, url, and url_path, the search query
>> would have to contain the complete host or url path.
>>
>> What is the best way to accomplish this?  Should I be looking at
>> building a custom dictionary that breaks down hosts and urls or is
>> there something simpler I can do?
>>
>>
>> Have you looked into trigrams?
>> http://www.postgresql.org/docs/current/static/pgtrgm.html
>>
>
> I've looked at it in the context of adding fuzzy search.  But my
> understanding is that doing a fuzzy search here would only work if the
> query were a significant fraction of, say, the url path.  For example, I
> would expect a fuzzy search of "foobar" on "/foobar/x" to return a high
> similarity, but a fuzzy search of "foobar" on "/foobar/some/very/long/path/x"
> to have a low similarity.
>
> Or are you suggesting using trigrams in a different way?
>

Yeah, I was thinking more along the lines of allowing wildcard searching,
not similarity.

Eg.
CREATE INDEX yourtable_yourcol_gist_fbi ON yourtable using GIST ( yourcol
gist_trgm_ops );
select * from yourtable where yourcol ~~ '%foobar%';

Re: [GENERAL] Full text search on partial URLs

[bricklen]

On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin
wrote:

> Hi,
>
> I have Postgres full text search set up for my application and it's been
> working great!  However, my users would like their searches to turn up
> parts of URLs.  For example, they would like a search for "foobar" to turn
> up a document that contains the string "http://example.com/foobar/blah";
> (and similarly for queries like "example" and "blah).  With the default
> dictionaries for host, url, and url_path, the search query would have to
> contain the complete host or url path.
>
> What is the best way to accomplish this?  Should I be looking at building
> a custom dictionary that breaks down hosts and urls or is there something
> simpler I can do?
>

Have you looked into trigrams?
http://www.postgresql.org/docs/current/static/pgtrgm.html

Re: [GENERAL] autovaccum task got cancelled

[bricklen]

On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu  wrote:

> Hello,
>
> I'm running an application (with programs in Perl) through pgpool 3.1 with
> replication mode to two postgresql db servers (version 9.0.13).  Recently,
> I noticed that the following messages repeatedly showed in postgres log
> files.  As far as I know, the application programs do not make any specific
> lock on the 'file' table.  I'm not sure if it is caused by the pgpool or
> something else.
>

Try setting your log_line_prefix so that more details are logged, that
might help track down where the locks etc are coming from.
Eg
log_line_prefix = '%m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x]
[%i] '

and reload your config (eg. "select pg_reload_conf()" as the superuser)

Re: [GENERAL] Need some help on Performance 9.0.4

[bricklen]

On Sun, Oct 13, 2013 at 8:43 AM, akp geek  wrote:

> Sorry all not posting clear.  I posted our postresql.conf file. What we
> are having is very slow response from the database.
>
> would like to get some feedback about postgresql.conf file parameters that
> I posted are causing the issue for performance of the DB.
>
> Our database is about 60GB and performance is very bad.
>
>
> pci, instance #5
> pci, instance #5
> load averages:  17.5,  18.1,  18.6;up
> 673+23:00:23
>
> 16:33:58
> 156 processes: 140 sleeping, 16 on cpu
> CPU states: 76.5% idle, 22.8% user,  0.7% kernel,  0.0% iowait,  0.0% swap
> Memory: 64G phys mem, 19G free mem, 63G total swap, 63G free swap
>
>
You aren't going to get much help without some specific details about what
is slow.
https://wiki.postgresql.org/wiki/Slow_Query_Questions

[GENERAL] Re: [GENERAL] Forms for entering data into postgresql‏

[bricklen]

On Wed, Oct 9, 2013 at 7:05 PM, Sudhir P.B.  wrote:

> I have developed an application using MS SQL. I have used MS Access for
> creating forms to enter data into the database. I am thinking of changing
> over to postgresql and would also like to use any other available open
> source tool for creating forms. Are there any free applications available
> for creating forms similar to the ones I have made in MS Access?. Any
> alternative suggestions will be appreciated.
>

Here is a list of tools that has been more or less maintained over the past
several years:
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
I have no idea how many of them are still actively developed or used though.

Re: [HACKERS] [GENERAL] Urgent Help Required

[bricklen]

On Tue, Oct 8, 2013 at 8:25 AM, shailesh singh wrote:

> yes i am executing psql to connect to this database.
>
> su - postgres
> psql patnadbold
>

..and then what? Does it immediately throw an error stating that you must
issue a VACUUM?

Re: [GENERAL] Many thousands of partitions

[bricklen]

On Tue, Oct 8, 2013 at 8:23 AM, Grzegorz Tańczyk wrote:

>  Hello,
>
> I have question regarding one of caveats from docs:
> http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html
>
>
If you are using Postgresql 8.3 then you should consider upgrading to 9.3
instead.


> "Partitioning using these techniques will work well with up to perhaps a
> hundred partitions; don't try to use many thousands of partitions."
>
> What's the alternative? Nested partitioning could do the trick?
>

Nested partitioning will have the same problems, if not more. The query
planner might come up with suboptimal plans depending on how many nested
partitions there are.


> I have milions of rows(numbers, timestamps and text(<4kb), which are
> frequently updated and there are also frequent inserts. Partitioning was my
> first thought about solution of this problem. I want to avoid long lasting
> locks, index rebuild problems and neverending vacuum.
> Write performance may be low if at the same time I will have no problem
> selecting single rows using primary key(bigint).Partitioning seems to be
> the solution, but I'm sure I will end up with several thousands of
> automatically generated partitions.
>


I can speak from painful experience: just recently we had a project where a
development team went ahead and partitioned about 900 tables, resulting in
almost 80 thousand tables . It was almost comical that every single query
went from sub-second to tens of seconds, and a pg_dump of an *empty*
database would take longer than an hour. This was on sandbox servers so the
hardware was not production grade, but it was an excellent way to get the
point across that too many partitions can crush performance.

Re: [HACKERS] [GENERAL] Urgent Help Required

[bricklen]

On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh wrote:

>
> On Tue, Oct 8, 2013 at 8:36 PM, bricklen  wrote:
>
>>
>> On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote:
>>
>>> HINT: To avoid a database shutdown, execute a full-database VACUUM in
>>> "patnadbold".
>>> ERROR: could not access status of transaction 33011
>>> DETAIL: could not open file "pg_clog/": No such file or directory
>>> exit
>>>
>>>
>>> After this i am able to stop /start my db server but i am not able to
>>> connect to my databases (it tells to run vacuum full first on patnadbold
>>> databases)
>>>
>>
>> The message does *not* say to run "VACUUM FULL", it says to run a
>> "full-database VACUUM". Different things.
>> Connect to "patnadbold" and issue "VACUUM;" (without double-quotes) as
>> the others have suggested.
>>
>
> When i am trying to connect "patnadbold" , it is giving error for "execute
> a full-database VACUUM in "patnadbold" " .
> is there any way to connect this database using backend process . pl let
> me know the command sequnce i need to run.
> Thanks.
>

Please keep replies CC'd to the pgsql-general list, and follow the format
of the other messages, which is to bottom-post.

You need to show (again?) exactly what you are executing. Are you
connecting via psql or using another command? Are you connecting directly
to that "patnadbold" database? If you are able to connect to it, are you
able to issue just "VACUUM;" ?

Re: [HACKERS] [GENERAL] Urgent Help Required

[bricklen]

On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote:

> HINT: To avoid a database shutdown, execute a full-database VACUUM in
> "patnadbold".
> ERROR: could not access status of transaction 33011
> DETAIL: could not open file "pg_clog/": No such file or directory
> exit
>
>
> After this i am able to stop /start my db server but i am not able to
> connect to my databases (it tells to run vacuum full first on patnadbold
> databases)
>

The message does *not* say to run "VACUUM FULL", it says to run a
"full-database VACUUM". Different things.
Connect to "patnadbold" and issue "VACUUM;" (without double-quotes) as the
others have suggested.

Re: [GENERAL] Fighting the autovacuumer (to prevent wraparound)

[bricklen]

On Thu, Oct 3, 2013 at 11:48 AM, Michael Graham  wrote:

> Hi all,
>
> We partition the data in postgres in a per-month basis and run a script
> to delete old partitions.


Does "delete" = "drop"?


>  Sometimes this script fails and the delete
> doesn't happen because of a deadlock, today I noticed that it was the
> autovacuumer that fighting with the script.
>

Are you uninheriting the partition before attempting to get rid of it?
Eg.
ALTER TABLE your_partition NO INHERIT the_parent_table;

Re: [GENERAL] how to know cpu utilization using sql command

[bricklen]

On Thu, Oct 3, 2013 at 1:50 AM, M Tarkeshwar Rao <
m.tarkeshwar@ericsson.com> wrote:

>   How to know cpu and memory utilization in postgres.
>
> We require this information to show it on our Application using any
> postgres command or tool.
>

pg_activity works pretty well:
https://github.com/julmon/pg_activity/

>

Re: [GENERAL] partitioning for speed, but query planner ignores

[bricklen]

On Wed, Oct 2, 2013 at 9:01 AM, David Rysdam  wrote:

>  I had some vague notion of tables "doing work" but really if it can load
> one
> partition into RAM and get most of my hits from there, it'd be a big
> win.


The same concept applies to the frequently-used indexes on that partition.

Re: [GENERAL] How do I find a trigger function that is raising notices?

[bricklen]

On Thu, Sep 26, 2013 at 9:04 AM, Rob Richardson wrote:

> Select * from pg_proc where lower(prosrc) = '%%'



A slight revision should work:

select distinct proname from pg_proc where prosrc ilike '%%';

Re: [GENERAL] how to tell master from replica

[bricklen]

On Wed, Sep 25, 2013 at 5:11 PM, bricklen  wrote:

>
> On Wed, Sep 25, 2013 at 4:53 PM, Scott Ribe 
> wrote:
>
>> Assuming a master & replica set up using streaming replication, hot
>> standby, 9.3.
>>
>> I'd like to have a single script on both machines distinguish whether
>> it's running on the machine with the master or replica, and take different
>> actions accordingly.
>>
>> Is the way to do this to check for the presence of wal sender process vs
>> wal receiver process? Or is there a query that could executed against sys
>> tables to find current running config of the local postmaster?
>>
>
> Would pg_is_in_backup() do the job?
>
>
> http://www.postgresql.org/docs/9.3/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP
>
>
Sorry, meant "pg_is_in_recovery()". (Michael's response tipped me off)

Re: [GENERAL] how to tell master from replica

[bricklen]

On Wed, Sep 25, 2013 at 4:53 PM, Scott Ribe wrote:

> Assuming a master & replica set up using streaming replication, hot
> standby, 9.3.
>
> I'd like to have a single script on both machines distinguish whether it's
> running on the machine with the master or replica, and take different
> actions accordingly.
>
> Is the way to do this to check for the presence of wal sender process vs
> wal receiver process? Or is there a query that could executed against sys
> tables to find current running config of the local postmaster?
>

Would pg_is_in_backup() do the job?

http://www.postgresql.org/docs/9.3/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP

Re: [GENERAL] Query runs forever after upgrading to 9.3

[bricklen]

On Mon, Sep 23, 2013 at 9:50 AM, Andrus  wrote:

>  http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> wrote that max reasonable value in Windows for shared_buffers is 512MB
> Is my setting shared_buffers= 2400MB reasonable in Windows ?
>

Someone else will hopefully answer that question, I have never run
Postgresql on Windows.

Re: [GENERAL] Query runs forever after upgrading to 9.3

[bricklen]

On Mon, Sep 23, 2013 at 9:12 AM, Andrus  wrote:

>
> work_mem = 400MB
>
> Hope that this is OK.
>

For cluster-wide setting you will probably want to drop that significantly
-- start lower, as in somewhere around 10MB and work up from there as
necessary. For the queries you are finding slow (the reason for these
emails) you can set work_mem specifically for the session.
Eg.
set work_mem to '400MB';

reset work_mem;

Re: [GENERAL] Query runs forever after upgrading to 9.3

[bricklen]

On Mon, Sep 23, 2013 at 8:33 AM, Andrus  wrote:

>   Hi!
>   >Could you also post the results of the following query?
> >SELECT name, current_setting(name), source
> >FROM pg_settings
> >WHERE source NOT IN ('default', 'override');
>  In real server where problem is:
>
>
> 21  shared_buffers 2400MB configuration file
>


What are effective_cache_size and work_mem set to? The defaults? They are
good candidates to be increased. effective_cache_size could be set to (for
example) 10GB, depending on how much memory gets consumed by the other
application(s) running on that server.

The EXPLAIN ANALYZE plan of your query will show if work_mem needs to be
increased, as there will be a line saying something like "External merge:
disk sort" (or something like that, can't recall the exact message off the
top of my head).



>
>  In development computer from where explain was posted and problem with
> copy of database also occurs:
>
> "shared_buffers";"128MB";"configuration file"
>


You likely want to bump that up closer to 1GB.



>
> > Also, what is the total memory in the server?
>


>  In devel computer where tests are performed, 4 GB
> Real server  has 16 GB RAM
> Real server  is for  Postgres for this database and ASP.NET MVC3
> application which uses this same database from postgres.
>

Re: [GENERAL] Query runs forever after upgrading to 9.3

[bricklen]

On Mon, Sep 23, 2013 at 8:06 AM, Andrus  wrote:

>
> >Could you please post EXPLAIN for that query?
>

Could you also post the results of the following query?

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

Also, what is the total memory in the server?

Re: [GENERAL] Why does this array query fail?

[bricklen]

On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer  wrote:

> Thanks for the explanation.  I think I at least understand what it's doing
> now.  I'm either surprised or confused though, as I was under the
> impression that you could substitute a subquery for a value pretty much
> anywhere, but I guess that's not the case?
>
> Cheers,
> Ken
>
>
Your subquery can also be explicitly casted to make it work. Note the
"::TEXT[]"

SELECT 'found' WHERE 'test' = ANY( (SELECT
ARRAY['test','pass','fail'])::TEXT[] );

Re: [GENERAL] Why does this array query fail?

[bricklen]

On Mon, Sep 16, 2013 at 5:32 PM, Ken Tanzer  wrote:

> SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pass','fail']) );
>


It works if you drop the inner SELECT.

SELECT 'found' WHERE 'test' = ANY( ARRAY['test','pass','fail'] );

Re: [GENERAL] fsync and wal_sync_method

[bricklen]

On Wed, Sep 11, 2013 at 6:11 PM, ascot.m...@gmail.com
wrote:

> Hi,
>
> I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and
> pg-Slave with streaming replication.
>
> The archive_command is enabled and the "rsync" is used in pg-Master to
> save all its archived WAL files to the 3rd machine for backup purpose,  by
> default, both fsync and wal_sync_method are commented out in
> postgresql.conf:
>
> archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq  %p pg@pg_slave
> :/usr/pg_arcxlog/%f'
> #fsync = on # turns forced
> synchronization on or off
> #wal_sync_method = fsync# the default is the first option
>
> Thus I think the latest WAL might not be flushed to disk from time to time
> in pg-Master, therefore the WAL saved in the 3rd server might not contain
> the latest WAL. In order to avoid possible data lost, should 'fsync" be
> turned ON in pg-Master and pg-Slave?  if yes what would be the best
> settings of "wal_sync_method" for these two servers respectively?
>
> Please advise.
> regards
>

I think you are confusing fsync and switching WAL segments for archiving
purposes. Here's a link to the docs to get you started:
http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT

fsync is enabled unless you disable it, the fact that it is commented out
means that it is set to its default ("on"). wal_sync_method at its default
is probably fine, but if you are interested, look at the pg_test_fsync
tool: http://www.postgresql.org/docs/9.2/static/pgtestfsync.html

Re: [GENERAL] OLAP

[bricklen]

On Tue, Aug 27, 2013 at 3:04 PM, Jerry Sievers wrote:

> Vertica is another case of an analytics focused platform that dirived
> from Postgres, version 8.0 IIRC.
>

FWIW,
You might be thinking of Greenplum, and I think it forked from pg 8.1.
Vertica was built from scratch, it is not using Postgres as the basis for
the db. This is according to the engineers that I have spoken to in the
past at Vertica. I believe there was some code ported over for their
command line client "vsql", but I could be wrong about that. At the very
least, vsql looks and acts like psql from a user's point of view.

I agree absolutely with you about the dazzling speed of certain types of
queries -- one of my tests was a simple aggregate query (a bunch of SUM()
and GROUP BY's) over about 500M rows and it returned results in a handful
of seconds. The same query in our Postgresql server with 2x the number of
disks took handfuls of minutes IIRC. On the flip side, I found some of the
complex Window functions performed on par or better in Postgresql than in
Vertica.

Re: [GENERAL] Update quey

[bricklen]

On Fri, Aug 23, 2013 at 8:04 AM, Hall, Samuel L (Sam) <
sam.h...@alcatel-lucent.com> wrote:

> I have a table (pubacc_lo) from the US government with 500,00+ rows. It
> has latitude and longitude in three columns each for degrees, minutes and
> seconds. I need a Point geometry column. So I wrote this query:
>
> with mydata AS (SELECT (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 +
> pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees +
> pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long FROM
> pubacc_lo)
> UPDATE pubacc_lo SET lonlat_84 = ST_SetSRID(ST_makePOINT(long,lat),4326)
> FROM mydata;
>
> It appears to work, but is going to take days it seems to finish. Anybody
> have a faster way?
>
>
Create a new table, rather than updating the existing one.

CREATE TABLE pubacc_lo_new AS
select *, (pubacc_lo.lat_degrees + pubacc_lo.lat_minutes/60 +
pubacc_lo.lat_seconds/3600) as lat , (pubacc_lo.long_degrees +
pubacc_lo.long_minutes/60 + pubacc_lo.long_seconds/3600) as long
from pubacc_lo;

Then either rename them, or use the new table.

Re: [GENERAL] Column names for INSERT with query

[bricklen]

On Thu, Aug 22, 2013 at 3:30 PM, Robert James wrote:

> I would like to:
>
> INSERT INTO t
> SELECT * FROM r, (x + y) AS field3
>

INSERT INTO t (col1, col2, col3)
SELECT ...

Re: [GENERAL] Temp files on Commit

[bricklen]

On Thu, Aug 22, 2013 at 8:40 AM, Tom Lane  wrote:

>
> There is some setting that controls whether such messages appear at
> all
>

Is it "log_temp_files"?

Re: [GENERAL] pg_get_triggerdef can't find the trigger using OID.

[bricklen]

On Fri, Aug 16, 2013 at 8:01 AM, AI Rumman  wrote:

> Why can't pg_get_triggerdef find the trigger using OID.
>
> testdb=# SELECT
> testdb-#   p.oid,
> testdb-#   n.nspname as "Schema",
> testdb-#   p.proname as "Name",
> testdb-#   pg_catalog.pg_get_function_result(p.oid) as "Result data type",
> testdb-#   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data
> types",
> testdb-#  CASE
> testdb-#   WHEN p.proisagg THEN 'agg'
> testdb-#   WHEN p.proiswindow THEN 'window'
> testdb-#   WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
> THEN 'trigger'
> testdb-#   ELSE 'normal'
> testdb-# END as "Type"
> testdb-# FROM pg_catalog.pg_proc p
> testdb-#  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
> testdb-# WHERE pg_catalog.pg_function_is_visible(p.oid)
> testdb-#   AND n.nspname <> 'pg_catalog'
> testdb-#   AND n.nspname <> 'information_schema'
> testdb-# ORDER BY 1, 2, 4;
>   oid  | Schema |  Name   | Result data type |
> Argument data types  |  Type
>
> ---++-+--+--+-
>  18249 | public | test_f  | trigger  |
>  | trigger
>
>
> testdb=# select pg_get_triggerdef(18249);
> ERROR:  could not find tuple for trigger 18249
>
> Thanks.
>

Is it because you need the oid from pg_trigger, rather than pg_proc?
The following query is a fragment of one I needed to put together the other
day and it might be useful to you (the last few SELECT columns are taken
from your query)

SELECT  DISTINCT
tr.oid,
n.nspname as schemaname,
c.relname as tablename,
tr.tgname as triggername,
pr.proname as function_name,
pg_catalog.pg_get_function_result(pr.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(pr.oid) as "Argument data
types",
CASE WHEN pr.proisagg THEN 'agg' WHEN pr.proiswindow THEN 'window'
WHEN pr.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
'trigger'   ELSE 'normal' END as "Type",
CASE WHEN pr.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
THEN pg_get_triggerdef(tr.oid) ELSE NULL END as trigger_def
FROM pg_catalog.pg_class as c
INNER JOIN pg_catalog.pg_attribute as a ON (a.attrelid = c.oid)
INNER JOIN pg_catalog.pg_type as t ON (t.oid = a.atttypid)
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tablespace ts ON ts.oid = c.reltablespace
LEFT JOIN pg_trigger tr ON tr.tgrelid::regclass::text = c.relname
LEFT JOIN pg_proc pr ON pr.oid = tr.tgfoid
WHERE a.attnum > 0  -- no system cols
AND NOT attisdropped-- no dropped cols
AND c.relkind = 'r'
AND tr.tgisinternal is not true
AND tr.tgname IS NOT NULL
ORDER BY n.nspname, c.relname

Re: [GENERAL] devide and summarize sql result (all)

[bricklen]

On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski  wrote:

> Hi,
>
> My sql query results sth. like this:
>
> user percentage
> franz 78%
> smith 98%
> franz 81%
> jason 79%
> smith 89%
> smith 85%
> smith 99%
>
> Now I'd like to summarize the percentages oder every user like this.
> smith
> 2 matches 95-100%
> 2 matches 85-95%
> 0 mathes 75-85%
>
> franz
> 0 mathes 95-100%
> ...
>

A CASE statement should work, if you are willing to hard-code the list of
expressions.

SELECT  username,
sum(case when avg between 76 and 85 then 1 else 0 end) as "76 to
85",
sum(case when avg between 86 and 95 then 1 else 0 end) as "86 to
95",
sum(case when avg > 95 then 1 else 0 end) as ">95"
FROM yourtable
GROUP BY username

Re: [GENERAL] bi-directional syncing help request

[bricklen]

On Fri, Aug 9, 2013 at 8:12 AM, Bèrto ëd Sèra wrote:

> or you can just take a full dump from one box and import it on the other
> any time you switch. If it's not a big db it should probably be quicker
> than any alternative approach.
>

A pg_dump from one system or the other will effectively overwrite the
database it is restored to, so any changes in one system or the other will
be lost unless processes are put in place to move that data to a staging
area before the reload.
The issue here is that it is a data and structure synchronization problem,
which isn't something solved very easily using Postgresql's core tools.
Synchronizing the data using Bash/Python, FDW's etc is reasonably
straightforward, but synchronizing the DDL changes will require some care.

Re: [GENERAL] bi-directional syncing help request

[bricklen]

On Fri, Aug 9, 2013 at 7:36 AM, Bèrto ëd Sèra wrote:

> --all you need to do is switch master and slave so that "master" is the
> one box you are currently on
>

That probably isn't a reasonable solution, considering the OP mentioned
that she was not a professional DBA. Setting up a hot/warm standby, failing
over, resetting the slave from the new master, lather, rinse, repeat  is
certainly scriptable but doesn't seem like the best option here.

I don't know of any commercial solutions, but writing some code might be
required. Synchronizing data isn't that difficult, but synchronizing
changes to database table structure will be a bit tricky,

Re: [GENERAL] setting high value for wal_keep_segments

[bricklen]

On Thu, Aug 8, 2013 at 6:23 AM, AI Rumman  wrote:

> Hi,
>
> I am going to sync slave with my master which is almost 500 G. I am not
> using archive directory instead of I am using wal files for streaming. As
> it may take almost 3 hours, I am thinking of setting up 400 for
> wal_keep_segments where I have enough space available.
>
> Without the space issue, could there be any other problem in setting up
> such high value for "wal_keep_segments"? As this is production, I need to
> confirmed.
>

Another data point: I set up SR on two systems recently in production with
the wal_keep_segments set to 1 (lots of logs were being generated), and
the slaves were about 1TB each. No problems were experienced.

Re: [GENERAL] Alter table never finishes

[bricklen]

On Tue, Jul 30, 2013 at 10:34 AM, bricklen  wrote:

> Sorry bricklen, I've killed all idle connections with "kill -9 ",
>
>> then I was able to execute the alter table.
>>
>
> No problem.
>

btw, I meant 'no need to apologize that the queries were gone', not that I
recommend "kill -9" (I don't!)

Re: [GENERAL] Alter table never finishes

[bricklen]

On Tue, Jul 30, 2013 at 10:29 AM, Leonardo M. Ramé wrote:

> > > select pg_cancel_backend(pid) from pg_stat_activity where pid <>
> > > pg_backend_pid();
>

As Giuseppe mentioned, if you need to kill not just the queries, but the
connection as well, you could use:

select pg_terminate_backend(pid) from pg_stat_activity where pid <>
pg_backend_pid();

That is much safer than kill -9, which if issued against the parent
postgres process will crash your cluster.


>
> Sorry bricklen, I've killed all idle connections with "kill -9 ",
> then I was able to execute the alter table.
>

No problem.

Re: [GENERAL] Alter table never finishes

[bricklen]

On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé wrote:

>
> select pg_cancel_backend(pid) from pg_stat_activity where pid <>
> pg_backend_pid();
>
> And it returned this:
>
> pg_cancel_backend
> ---
>  t
>  t
> (2 rows)
>
> But when I execute my update table command, it still never ends...Any
> hint?.
>
>
Sounds like locking issues. In another session -- other than the one you
are trying to run your update, what does the following query show?

SELECT
waiting.locktype   AS waiting_locktype,
waiting.relation::regclass AS waiting_table,
waiting_stm.query  AS waiting_query,
waiting.mode   AS waiting_mode,
waiting.pidAS waiting_pid,
other.locktype AS other_locktype,
other.relation::regclass   AS other_table,
other_stm.queryAS other_query,
other.mode AS other_mode,
other.pid  AS other_pid,
other.granted  AS other_granted
FROM pg_catalog.pg_locks AS waiting
JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid =
waiting.pid)
JOIN pg_catalog.pg_locks AS other ON ((waiting."database" =
other."database" AND waiting.relation  = other.relation) OR
waiting.transactionid = other.transactionid)
JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
WHERE NOT waiting.granted
AND waiting.pid <> other.pid;

Re: [GENERAL] Fwd: corrupted files

[bricklen]

On Tue, Jul 30, 2013 at 8:18 AM, Klaus Ita  wrote:

>
> thank you! It turned out that really there was a corruption in the main pg
> server which was 'virally' propagated to
>
> 1. streaming replica
> 1. replaying wal receiver
> 1. old backup that tried to replay the wal's
>
> I really thought with a master and 3 backups i'd be safe.
>


Physical corruption in the master, or logical?

Re: [GENERAL] Alter table never finishes

[bricklen]

On Tue, Jul 30, 2013 at 7:50 AM, Leonardo M. Ramé wrote:

> Hi, I need to do an alter table on a small table (~300 records), but it
> never ends. It may be because there are clients using that table.
>
> How can I force disconnect all clients to let me alter that table?.
>

If you are using PostgreSQL 9.2+, you can use this:
select pg_cancel_backend(pid) from pg_stat_activity where pid <>
pg_backend_pid();

If you are using earlier version, replace "pid" with "procpid".

That query will cancel all queries other than the session issuing the
pg_cancel_backend() calls.

Re: [GENERAL] Fwd: corrupted files

[bricklen]

On Mon, Jul 29, 2013 at 11:50 PM, Klaus Ita  wrote:

> I am trying to remember, there was a tool that plotted the contents of the
> wal_files in a more readable format ...
>

xlogdump?

https://github.com/snaga/xlogdump

Re: [GENERAL] 5 is not a smallint but '5' is

[bricklen]

On Mon, Jul 29, 2013 at 8:11 AM, John DeSoi  wrote:

> I was surprised to discover this today. I can work around it, but it seems
> counterintuitive. If 5 can't be parsed as a smallint, I would feel better
> if '5' was not one either.
>
>
> temp=# create or replace function itest (param smallint) returns integer
> as $$ select $1 + 5; $$ language sql;
> CREATE FUNCTION
> temp=# select itest(5);
> ERROR:  function itest(integer) does not exist
> LINE 1: select itest(5);
>^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
> temp=# select itest('5');
>  itest
> ---
> 10
> (1 row)
>
>

FWIW, It works if you don't rely on the implicit cast, and explicitly cast
it to smallint:

select itest(5::smallint);
 itest
---
10

Re: [GENERAL] Rule Question

[bricklen]

On Thu, Jul 25, 2013 at 4:18 AM, Giuseppe Broccolo <
giuseppe.brocc...@2ndquadrant.it> wrote:

> (TG_OP = 'UPDATE' AND
> (NEW.b != OLD.b OR
> (NEW.b IS NULL AND OLD.b IS NOT NULL) OR
> (NEW.b IS NOT NULL AND OLD.b IS NULL)
> )
> ) THEN
>



Unrelated to the OP's question, the suggestion above could be more simply
rewritten as

TG_OP = 'UPDATE'
AND NEW.b IS DISTINCT FROM OLD.b

Re: Reply: [GENERAL] Can we specify transaction level

[bricklen]

On Fri, Jul 19, 2013 at 5:08 PM, guxiaobo1982  wrote:

>
> what does a wrapper function mean, count is a standard function in Greenplum, 
> or how to creat the wrapper function.
>
> regards
>
> On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982  wrote:
>
>  There is another situation,
>
>
> We have a demo table with about 1700 rows,  the "select count(1) from  
> demotable" statement finishes with-in 3  seconds when executed directlly 
> against the Greenplum database,but it takes about 230 seconds to finish when 
> executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it may 
> because of postgres_fdw pulling data to the PostgreSQL instance and counts 
> the rows there, but I think the query optimizer should pass through the 
> count() function to the Greenplum end, and gets only the result back.
>
>
> Are you able to create a function in Greenplum which is a wrapper around that 
> count(*) and call that via the postgres_fdw?
>

Create a function in Greenplum like (untested, I don't have Greenplum)

create table custom_count() returns bigint as $$
select count(*) as total from yourtable;
$$ language sql;

Then from your pg db using postgres_fdw select * from that function (which
resides in Greenplum).

No idea if that'll work or make a difference though.

Re: Reply: [GENERAL] Can we specify transaction level when connectting toexternal postgresql server via postgres_fdw

[bricklen]

On Fri, Jul 19, 2013 at 7:47 AM, guxiaobo1982  wrote:

> There is another situation,
>
> We have a demo table with about 1700 rows,  the "select count(1) from
> demotable" statement finishes with-in 3  seconds when executed directlly
> against the Greenplum database,but it takes about 230 seconds to finish
> when executed via postgres_fdw inside PostgreSQL 9.3 beta2, I guess that it
> may because of postgres_fdw pulling data to the PostgreSQL instance and
> counts the rows there, but I think the query optimizer should pass through
> the count() function to the Greenplum end, and gets only the result back.
>

Are you able to create a function in Greenplum which is a wrapper around
that count(*) and call that via the postgres_fdw?

Re: [GENERAL] Update big table

[bricklen]

On Mon, Jul 15, 2013 at 6:08 AM, Haiming Zhang  wrote:

> I run my query using JOIN for two hours, and did not get it done. Here is
> my query:
>
> update table1 set col1 = true from table2 where table1.event_id =
> table2.event_id
>

Did you already post the query plan from "EXPLAIN update table1 set col1 =
true from table2 where table1.event_id = table2.event_id" ?

Re: [GENERAL] decrease my query duration

[bricklen]

On Thu, Jul 4, 2013 at 5:26 PM, David Carpio wrote:

>  Hello All
>
> I am newbie in this forum that's why I will be very grateful if would be
> able to help me and if you need some additional information please let me
> know.
> I have a query that I would like decrease its duration
>



Have a look at https://wiki.postgresql.org/wiki/Slow_Query_Questions and
supply a few more details, it will increase your chances of getting helpful
answers.
Also, can you supply the EXPLAIN (ANALYZE, BUFFERS) plan instead of the
simple EXPLAIN plan?

Re: [GENERAL] Database performs massive reads when I'm doing writes.

[bricklen]

On Thu, Jun 6, 2013 at 9:53 AM, Ray Cote
wrote:

> Starting a few days ago we started to see a strange behavior where writing
> to the database causes massive read operations.
> For example, I have a table that needs to be updated every night (about
> 20,000 rows).
> Using Django ORM, we update them one item at a time.
> Usually the overall process takes a few minutes; it is now taking hours
> (like over 15 hours).
>
> Running atop, we're seeing Read Disk values in the range of 147.2M/10s and
> Write Disk values in the range of 16K/10s.
> Together, the Disk throughput is in the high 90% and frequently hits 100%.
>
> When I'm not writing to the database, it becomes quiet without any disk
> activity.
>
> This is a 256 MByte system and atop shows we're not swapping
>


Is autovacuum set to 'on'?
Possibly there is bloat in your table and indexes; what happens to the
system after you issue a manual "VACUUM ANALYZE VERBOSE"? If that helps,
you might need to make your autovacuum settings more aggressive.

Re: [GENERAL] search_path for database vs user vs session

[bricklen]

On Mon, May 27, 2013 at 12:16 PM, Moshe Jacobson  wrote:

> Oh, and I'd also like to see the current setting of the database so I know
> what will happen if I clear the user setting.


I think you can find some of what you are looking for here:
http://www.postgresql.org/docs/9.2/static/view-pg-settings.html

Re: [GENERAL] Random numbers

[bricklen]

On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar <
kesco...@estudiantes.uci.cu> wrote:

> I want to generate random numbers in Pl/pgSQL. How can I do this?
> To be more specific, I have to generate random numbers among 0 and 5.
>

One way:
select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() limit 1;

Re: [GENERAL] bloating vacuum

[bricklen]

On Tue, May 14, 2013 at 7:51 AM, S H  wrote:

>   I created small table and started doing insertion/deletion/updation on
> 2 rows in infinite loop. It started bloating around 844 times, but after it
> stopped bloating.. what could be the reason?
>

Did autovacuum kick in and clean up the table?
Is autovacuum on? Type:
show autovacuum;

You can watch the dead tuple count  using *pgstattuple (an extension which
can be installed via "create extension pgstattuple;".
Once installed,
select * from pgstattuple('name_of_your_table');

*http://www.postgresql.org/docs/current/static/pgstattuple.html


> In between i am running manual vacuum analyze ( without full option)
>
 After every iteration?

>

Re: [GENERAL] Error installation of openmolar

[bricklen]

Those are not postgresql errors. Have you tried the openmolar mailing list
at https://groups.google.com/forum/?fromgroups#!forum/openmolar ?


On Mon, Apr 22, 2013 at 6:05 PM, elmekki mahdhaoui  wrote:

> Hello everyone
>
> I tried to install openmolar  on my ubuntu
> lucid system following instructions in this page <
> http://www.openmolar.com/docs/installation/ubuntu.html>
>
> Some errors appears during the installation, and this is the image of
> errors 
>
> The program starts normally, but I can not create a new database ...
>
> Help me, please
>
>
>

Re: [GENERAL] View to show privileges on views/tables/sequences/foreign tables

[bricklen]

On Thu, Feb 21, 2013 at 9:38 AM, bricklen  wrote:
> A while back I was looking for a way to display object privileges
> quickly with a bit better readibility. The following view is what I
> came up with. Suggestions and improvements welcome (or comments
> stating that there are much easi\er ways to get the same details).

The information schema exposes a lot of the same information, so it
might be more portable to query from there rather than using the
object_privileges view.
Eg. for table privileges:

select * from information_schema.table_privileges where table_name =
'event' limit 1;
-[ RECORD 1 ]--+-
grantor| postgres
grantee| PUBLIC
table_catalog  | testdb
table_schema   | public
table_name | event
privilege_type | INSERT
is_grantable   | YES
with_hierarchy | NO


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] View to show privileges on views/tables/sequences/foreign tables

[bricklen]

A while back I was looking for a way to display object privileges
quickly with a bit better readibility. The following view is what I
came up with. Suggestions and improvements welcome (or comments
stating that there are much easi\er ways to get the same details).

(was created in a utility "admin" schema)

create or replace view admin.object_privileges as
select  objtype,
schemaname,
objname,
owner,
objuser,
privs,
string_agg(
(case   privs_individual
when 'arwdDxt' then 'All'
when '*' then 'Grant'
when 'r' then 'SELECT'
when 'w' then 'UPDATE'
when 'a' then 'INSERT'
when 'd' then 'DELETE'
when 'D' then 'TRUNCATE'
when 'x' then 'REFERENCES'
when 't' then 'TRIGGER'
when 'X' then 'EXECUTE'
when 'U' then 'USAGE'
when 'C' then 'CREATE'
when 'c' then 'CONNECT'
when 'T' then 'TEMPORARY'
else 'Unknown: '||privs end
), ', ' ORDER BY privs_individual) as privileges_pretty
from(select objtype,
schemaname,
objname,
owner,
privileges,
(case when coalesce(objuser,'') is not distinct from
'' then 'public' else objuser end)
|| (case when pr2.rolsuper then '*' else '' end)
as objuser,
privs,
(case   when privs in ('*','arwdDxt') then privs
else regexp_split_to_table(privs,E'\\s*')
end) as privs_individual
from(select distinct
objtype,
schemaname,
objname,
coalesce(owner,'') || (case when pr.rolsuper
then '*' else '' end) as owner,
regexp_replace(privileges,E'\/.*','') as privileges,

(regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[1]
as objuser,

(regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[2]
as privs
from(SELECT n.nspname as schemaname,
c.relname as objname,
CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as objtype,

regexp_split_to_table(array_to_string(c.relacl,','),',') as
privileges,
pg_catalog.pg_get_userbyid(c.relowner) as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S', 'f')
AND n.nspname !~ '(pg_catalog|information_schema)'
--AND pg_catalog.pg_table_is_visible(c.oid) /*
Uncomment to show only objects */
) as y  /*
visible in search path */
left join pg_roles pr on (pr.rolname = y.owner)
) as p2
left join pg_roles pr2 on (pr2.rolname = p2.objuser)
--where coalesce(p2.objuser,'') is distinct from '' /*
Uncomment to hide "public" role */
) as p3
group by objtype, schemaname,objname, owner, objuser, privs
order by objtype,schemaname,objname,objuser,privileges_pretty;

comment on column admin.object_privileges.owner is '"*" after the
owner indicates that the owner is a superuser';
comment on column admin.object_privileges.objuser is '"*" after the
objuser indicates that the objuser is a superuser';


select * from admin.object_privileges limit 10;

 objtype  | schemaname |objname  |   owner   |
objuser|  privs  |   privileges_pretty
--++-+---+-+-+
 sequence | public | event_id_seq| postgres* |
postgres*   | rwU | SELECT, USAGE, UPDATE
 sequence | public | event_id_seq| postgres* | foobar
| rw  | SELECT, UPDATE
 table| public | network_events  | postgres* |
postgres*   | arwdDxt | All
 table| public | network_events  | postgres* | foobar
| ar  | INSERT, SELECT
 table| public | network_events_201301   | postgres* |
postgres*   | arwdDxt | All
 table| public | network_events_201301   | postgres* | foobar
| arwd| INSERT, DELETE, SELECT, UPDATE
 table| public | network_events_201302   | postgres* |
postgres*   | arwdDxt | All
 table| public | network_events_201302   | postgres* | foobar
| arwd| INSERT, DELETE, SELECT, UPDATE
 table| public | network_events_20130211 | postgres* |
postgres*   | arwdDxt | All
 table| public | event   | postgres* | foobar
|