Re: [GENERAL] pg on Debian servers

[Magnus Hagander]

On Sat, Nov 11, 2017 at 2:23 PM, rob stone <floripa...@gmail.com> wrote:

>
>
> On Sat, 2017-11-11 at 13:03 +, Mark Morgan Lloyd wrote:
> > Apologies for something which is distro related, but I was bitten by
> > a
> > "silly mistake"- one of my own, I hasten to say- earlier.
> >
> > Several legacy programs written in Delphi ground to a halt this
> > morning,
> > which turned out to be because a Debian system had updated its copy
> > of
> > PostgreSQL and restarted the server, which broke any live
> > connections.
> >
> > At least some versions of Delphi, not to mention other IDE/RAD tools
> > with database-aware components, don't automatically try to
> > reestablish a
> > database session that's been interrupted. In any event, an
> > unexpected
> > server restart (irrespective of all investment in UPSes etc.) has
> > the
> > potential of playing havoc on a clustered system.
> >
> > Is there any way that either the package maintainer or a site
> > administrator/programmer such as myself can mark the Postgres server
> > packages as "manual upgrade only" or similar? Or since I'm almost
> > certainly not the first person to be bitten by this, is there a
> > preferred hack in mitigation?
> >
> > --
> > Mark Morgan Lloyd
> > markMLl .AT. telemetry.co .DOT. uk
> >
> > [Opinions above are the author's, not those of his employers or
> > colleagues]
> >
> >
>
> Hello Mark,
>
> Probably caused by systemd.


Systemd has nothing to do with it, it's Debian standard to restart the
services when the binaries have changed, regardless of sysvinit or systemd.


> You can disable the postgresql service and
> re-name the script in init.d. You then have to start postgres via a
> shell script.
>

The init.d script is not used with systemd.



> You can also mark packages to be on "hold" but I don't know exactly
> what happens for major version upgrades as the current version is 9 but
> when you run an upgrade via apt it will try to install version 10 which
> is no big deal as the binaries will end up in different paths, however
>

The current version is 10. The previous version was 9.6. Version 9 was more
than 5 years ago.

And the apt system will *never* try to upgrade across a major version. You
do a new install to get the new version. An upgrade operation will put you
at the latest minor release for the currently installed version.



> libpq will be updated and that may cause a restart. I run upgrades
> without any applications running so I don't know exactly what could
> happen when using unattended upgrades.
>

libpq does get upgraded, but it does not cause restarts. A restart of a
client application using libpq must be done manually by the administrator
(unless there is specific code in the client application or it's packaging
to deal with that).


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] pg on Debian servers

[Magnus Hagander]

On Sat, Nov 11, 2017 at 2:03 PM, Mark Morgan Lloyd <
markmll.pgsql-gene...@telemetry.co.uk> wrote:

> Apologies for something which is distro related, but I was bitten by a
> "silly mistake"- one of my own, I hasten to say- earlier.
>
> Several legacy programs written in Delphi ground to a halt this morning,
> which turned out to be because a Debian system had updated its copy of
> PostgreSQL and restarted the server, which broke any live connections.
>
> At least some versions of Delphi, not to mention other IDE/RAD tools with
> database-aware components, don't automatically try to reestablish a
> database session that's been interrupted. In any event, an unexpected
> server restart (irrespective of all investment in UPSes etc.) has the
> potential of playing havoc on a clustered system.
>
> Is there any way that either the package maintainer or a site
> administrator/programmer such as myself can mark the Postgres server
> packages as "manual upgrade only" or similar? Or since I'm almost certainly
> not the first person to be bitten by this, is there a preferred hack in
> mitigation?


Certainly. Unrelated to PostgreSQL, this is a standard feature in Debian.
Commonly used to prevent things like kernel upgrades from happening on the
same schedule as others.

Basically, you put the package "on hold". See the debian administratino
guide at
https://debian-administration.org/article/67/Preventing_Debian_Package_Upgrades


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Logcal replication in large scale

[Magnus Hagander]

On Wed, Sep 20, 2017 at 10:02 AM, Nicklas Avén <nicklas.a...@jordogskog.no>
wrote:

> On Wed, 2017-09-20 at 09:44 +0200, Magnus Hagander wrote:
>
> On Wed, Sep 20, 2017 at 8:53 AM, Nicklas Avén <nicklas.a...@jordogskog.no>
> wrote:
>
> Hallo all
>
> I am thrilled about logical replication in PostgreSQL 10. My head have
> started spinning about use cases.
>
> Would it be possible to use logical replication as a distribution
> method of data?
>
>
> As an answer to the generic  question: yes :)
>
>
>
> I think about map data from national mapping authorities. The problem
> is to get the updates of their data sets. Especially the open data sets
> are now distributed as files (shape files) in Sweden and as pg_dump in
> Norway.
>
> I guess it is beyond what logical replication is designed for, so I ask
> what problems that might arise for a scenario like:
>
> The distributor has a publication database with logical replication
> publications of the tables. All users, probably thousands or more,
> would subscribe to that publication to get an updated copy of the data
> set.
>
> How would the publication server react? I guess the WAL-files will only
> be written once anyway?
>
>
> Yes. But they will  need to be kept around until *all* subscribers have
> pulled down their changes. So even one subscriber falling behind will mean
> your WAL will never get cleaned up.
>
> Of course, you can keep some sort of watcher process that kills old
> replication slots.
>
> I am also not sure how well PostgreSQL will react to having thousands of
> replication slots. It's not what the system was designed for I believe :)
>
>
> Ok, I have to read me up on how this works. I thought about it as a bucket
> of WAL-files that the subscribers just "grab".
> But of course there is some bookkeeping to make things work.
>

No, it's streaming replication.

And in the end, that bucket becomes infinitely large.


>
> I guess there is also mechanisms so a new subscriber can get a complete
> table after the publcation have been active for a long time? I mean the
> "cleaning up" of Wal-files will not leave new subscribers missing what is
> written to the table long ago?
>

Yes, new subscriptions will get the current version of the data and only
then start buffering changes.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Logcal replication in large scale

[Magnus Hagander]

On Wed, Sep 20, 2017 at 8:53 AM, Nicklas Avén 
wrote:

> Hallo all
>
> I am thrilled about logical replication in PostgreSQL 10. My head have
> started spinning about use cases.
>
> Would it be possible to use logical replication as a distribution
> method of data?
>

As an answer to the generic  question: yes :)



> I think about map data from national mapping authorities. The problem
> is to get the updates of their data sets. Especially the open data sets
> are now distributed as files (shape files) in Sweden and as pg_dump in
> Norway.
>
> I guess it is beyond what logical replication is designed for, so I ask
> what problems that might arise for a scenario like:
>
> The distributor has a publication database with logical replication
> publications of the tables. All users, probably thousands or more,
> would subscribe to that publication to get an updated copy of the data
> set.
>
> How would the publication server react? I guess the WAL-files will only
> be written once anyway?
>

Yes. But they will  need to be kept around until *all* subscribers have
pulled down their changes. So even one subscriber falling behind will mean
your WAL will never get cleaned up.

Of course, you can keep some sort of watcher process that kills old
replication slots.

I am also not sure how well PostgreSQL will react to having thousands of
replication slots. It's not what the system was designed for I believe :)

You might be better of using logical decoding (which goes back to 9.4) to
stream the data out, but not having each subscriber be a postgresql
subscriber. Either using it to generate some sort of "diff files" that can
then be consumed downstream, or by distributing it via some kind of
dedicated queuing system designed to handle that many downstreams.



> My guess is that it will be a lower payload than today anyway when the
> whole data set have to be fetched to get updates.
>

As always that's going to depend on the number of updates. If the same
thing is updated 1000 times, then if you use logical replication it gets
transferred 1000 times. So there are definitely cases when there will be
*more* traffic with logical replication, but in cases like yours I would
guess it will be less.

//Magnus

Re: [GENERAL] pg_ident mapping Kerberos Usernames

[Magnus Hagander]

On Sat, Sep 9, 2017 at 6:44 PM, <techmail+pg...@dangertoaster.com> wrote:

> Hi,
>
> I'm trying to get pg_ident to map "user1" and "us...@a.domain.tld" to
> "user1" in postgres, or vice versa. I'm not picky about which way works.
>
> Kerberos authentication works. I've gotten "user1" to login successfully
> with a Kerberos ticket, but I'm not able to get "us...@a.domain.tld" to
> match.
>
> Environment:
> * PostgreSQL 9.6 from PostgreSQL repos
> * CentOS 7
> * FreeIPA for Kerberos, LDAP, etc.
> * Realm A.DOMAIN.TLD
> * "user1" database exists
> * "user1" role exists
> * Logging into CentOS usernames are configured to drop the domain, so they
> appear as "user1" rather then "us...@a.domain.tld".
>
>
> pg_hba.conf:
>
> local   all postgrespeer
> hostall all 127.0.0.1/32md5
> hostall all ::1/128 md5
> hostall all 192.168.1.0/24  gss
> include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD #This is on one line.
> Thunderbird is truncating lines.
>
>
> pg_ident.conf:
>
> testnet/^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$\1
> testnet/^([0-9A-Za-z_-]+)$ \1
>
>
> Regex that works for both in regexr.com:
>
> /^([0-9A-Za-z-_]+)(@A\.DOMAIN\.TLD)?$/gm
>
>
> Command and lines from pg_log:
>
> $ psql -h db0 # Logged in as user1 with Kerberos ticket
>
> < 2017-09-09 19:50:49.376 CDT - 192.168.1.201 [unknown] > LOG: connection
> received: host=192.168.1.201 port=44918
> < 2017-09-09 19:50:49.398 CDT - 192.168.1.201 user1 > LOG:  connection
> authorized: user=user1 database=user1
> < 2017-09-09 19:50:50.912 CDT - 192.168.1.201 user1 > LOG: disconnection:
> session time: 0:00:01.537 user=user1 database=user1 host=192.168.1.201
> port=44918
>
> $ psql -h db0 -U us...@a.domain.tld # Logged in as user1 with Kerberos
> ticket
>
> < 2017-09-09 19:50:54.959 CDT - 192.168.1.201 [unknown] > LOG: connection
> received: host=192.168.1.201 port=44920
> < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 us...@a.domain.tld > LOG:
> no match in usermap "testnet" for user "us...@a.domain.tld" authenticated
> as "us...@a.domain.tld"
> < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 us...@a.domain.tld >
> FATAL:  GSSAPI authentication failed for user "us...@a.domain.tld"
> < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 us...@a.domain.tld >
> DETAIL:  Connection matched pg_hba.conf line 87: "host   all
> all 192.168.1.0/24  gss include_realm=1
> map=testnet krb_realm=A.DOMAIN.TLD"
>
>
> Is this something that is possible, or is it something where I need to
> pick one way to do it?
>

This looks like you are trying to connect with the actual username
user1¡A.DOMAIN.TLD. pg_ident only sets what you are allowed to log in as,
not what it will attempt.

If you are using psql, you are probably doing something like "psql -h
myserver". You need to add the user, so "psql -h myserver -U user1", to
instruct it of which username to actually use for the login.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] pg_rewind - restore new slave failed to startup during recovery

[Magnus Hagander]

On Tue, Aug 22, 2017 at 3:06 AM, Michael Paquier <michael.paqu...@gmail.com>
wrote:

> On Tue, Aug 22, 2017 at 9:52 AM, Dylan Luong <dylan.lu...@unisa.edu.au>
> wrote:
> > 1.   Disable the master ltm member (all connections redired to slave
> > member)
> > 2.   Promote slave (touch promote.me)
> > 3.   Stop the master db (old master)
> > 4.   Do pg_rewind on the new slave (old master)
> > 5.   Start the new slave.
>
> That flow looks correct to me. No I think that you should trigger
> manually a checkpoint after step 2 on the promoted standby so as its
> control file gets forcibly updated correctly with its new timeline
> number. This is a small but critical point people usually miss. The
> documentation of pg_rewind does not mention this point when using a
> live source server, and many people have fallen into this trap up to
> now... We should really mention that in the docs. What do others
> think?
>

If the documentation is missing such a clearly critical step, then I would
say that's a definite documentation bug and it needs to be fixed. We can't
really fault people for missing a small detail if we didn't document the
small detail...

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] debugging SSL connection problems

[Magnus Hagander]

On Mon, Jul 10, 2017 at 11:19 PM, Jeff Janes <jeff.ja...@gmail.com> wrote:

>
> Is there a way to get libpq to hand over the certificate it gets from the
> server, so I can inspect it with other tools that give better diagnostic
> messages?  I've tried to scrape it out of the output of "strace -s8192",
> but since it is binary it is difficult to figure out where it begins and
> ends within the larger server response method.
>
>
PQgetssl() or PQsslStruct() should give you the required struct from
OpenSSL, which you can then use OpenSSL to inspect. You should be able to
use (I think) SSL_get_peer_certificate() to get at it.

(this is what libpq does and stores it in ->peer, but that's a private api.
But you can see be-secure-openssl.c for some examples)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] pg_basebackup error: replication slot "pg_basebackup_2194" already exists

[Magnus Hagander]

On Tue, May 30, 2017 at 9:14 PM, Ludovic Vaugeois-Pepin <ludovi...@gmail.com
> wrote:

> I ran into the issue described below with 10.0 beta. The error I got is:
>
> pg_basebackup: could not create temporary replication slot
> "pg_basebackup_2194": ERROR:  replication slot "pg_basebackup_2194"
> already exists
>
> A race condition? Or maybe I am doing something wrong.
>
>
>
>
>
> Release:
> Name: postgresql10-server
> Version : 10.0
> Release : beta1PGDG.rhel7
>
>
> Test Type:
> Functional testing of a pacemaker resource agent
> (https://github.com/ulodciv/pgha)
>
>
> Test Detail:
> During context/environement setup, pg_basebackup is invoked (in
> parallel) from multiple virtual machines. The backups are then started
> as asynchronously replicated hot standbies.
>
>
> Platform:
> Centos 7.3
>
>
> Installation Method:
> yum -y install
> https://download.postgresql.org/pub/repos/yum/testing/10/
> redhat/rhel-7-x86_64/pgdg-redhat10-10-1.noarch.rpm
> yum -y install postgresql10-server postgresql10-contrib
>
>
> Platform Detail:
>
>
> Test Procedure:
>
> Have pg_basebackup run simultaneously on multiple hosts against
> the same instance eg:
>
> pg_basebackup -h test4 -p 5432 -D /var/lib/pgsql/10/data -U repl1
> -Xs
>
>
> Failure?
>
> E   deploylib.deployer_error.DeployerError:
> postgres@test5: got exit status 1 for:
> E   pg_basebackup -h test4 -p 5432 -D
> /var/lib/pgsql/10/data -U repl1 -Xs
> E   stderr: pg_basebackup: could not create temporary
> replication slot "pg_basebackup_2194": ERROR:  replication slot
> "pg_basebackup_2194" already exists
> E   pg_basebackup: child process exited with error 1
> E   pg_basebackup: removing data directory
> "/var/lib/pgsql/10/data"
>
>
> Test Results:
>
>
> Comments:
> This seems to be new with 10. I recently began testing the
> pacemaker resource agent against PG 10. I never had (or noticed) this
> failure with 9.6.1 and 9.6.2.
>

Hah, that's an interesting failure. In the name of the slot, the 2194 comes
from the pid -- but it's the pid of pg_basebackup.

I assume you're not running the two pg_basebackup processes on the same
machine? Is it predictable when this happens (meaning that the pid value is
actually predictable), or do you have to run it a large numbe rof times
before it happens?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

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

[Magnus Hagander]

On Tue, May 16, 2017 at 10:00 AM, Devrim Gündüz <dev...@gunduz.org> wrote:

>
> Hi,
>
> On Mon, 2017-05-15 at 22:35 -0700, Ken Tanzer wrote:
> > https://redmine.postgresql.org/issues/2409
>
> Not sure whether we should *fix* this or not on RPM side. This may break
> some
> of the existing installations, right?
>
> I'm not objecting, just asking for opinions.
>
>
Changing that in a minor version seems like a *really* bad idea, because
things *will* break. The way it is now it only breaks in case of a major
version upgrade, and there is an easy enough workaround present.

But it should perhaps be more clearly documented somewhere.


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Convert from LATIN9 to UTF-8 / WAL Replication

[Magnus Hagander]

On Wed, May 10, 2017 at 5:05 AM, Fabrízio de Royes Mello <
fabri...@timbira.com.br> wrote:

>
> Em ter, 9 de mai de 2017 às 17:40, basti <mailingl...@unix-solution.de>
> escreveu:
>
>> Hello,
>>
>> I must convert a Latin9 Pg-cluster (Version 9.1) in utf-8 with minimal
>> downtime, if possible.
>> My idea is to use WAL replication for that.
>> Is it usable for that or in other words, can WAL Replication handle
>> different encoding on master/slave?
>
>
> Short answer: no!
>
>
> Is there perhaps an other way to do that?
>
>
> I never tried it, but you can use some logical replication tool like Slony.
>
>
> Is it possible to use on Master Version 9.1 and on Slave 9.6 (it will
>> get the new master in the next step)?
>>
>>
> Unfortunately no because on 9.1 version we don't have support for logical
> replication yet. We start supporting it since 9.4 using pglogical
> extension. As I said before you should consider use one external
> replication tool like Slony to do this job.
>
>
In a scenario like this it might also be worth upgrading to 9.6 using
pg_upgrade while maintaining the latin9 encoding, and then do a second
migratino which changes the encoding from 9.6 w latin9 to 9.6 w utf8. That
way you can get access to the newer tools such as pglogical to help you do
that.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Link errors

[Magnus Hagander]

On Fri, May 5, 2017 at 1:30 AM, Igor Korot <ikoro...@gmail.com> wrote:

> Hi,
> [code]
> 1> Creating library vc_mswuddll\postgres_dll.lib and object
> vc_mswuddll\postgres_dll.exp
> 1>libpqd.lib(fe-connect.obj) : error LNK2019: unresolved external
> symbol __imp__WSAIoctl@36 referenced in function _setKeepalivesWin32
> 1>libpqd.lib(ip.obj) : error LNK2001: unresolved external symbol
> __imp__WSAIoctl@36
> 1>libpqd.lib(fe-connect.obj) : error LNK2019: unresolved external
> symbol __imp__DeleteSecurityContext@4 referenced in function
> _closePGconn
> 1>libpqd.lib(fe-connect.obj) : error LNK2019: unresolved external
> symbol __imp__FreeCredentialsHandle@4 referenced in function
> _closePGconn
> 1>libpqd.lib(fe-auth.obj) : error LNK2019: unresolved external symbol
> _FreeContextBuffer@4 referenced in function _pg_SSPI_continue
> 1>libpqd.lib(fe-auth.obj) : error LNK2019: unresolved external symbol
> _InitializeSecurityContextA@48 referenced in function
> _pg_SSPI_continue
> 1>libpqd.lib(fe-auth.obj) : error LNK2019: unresolved external symbol
> _AcquireCredentialsHandleA@36 referenced in function _pg_SSPI_startup
> 1>libpqd.lib(ip.obj) : error LNK2019: unresolved external symbol
> __imp__WSASocketA@24 referenced in function _pg_foreach_ifaddr
> 1>..\dbhandler\vc_mswuddll\postgres_dll.dll : fatal error LNK1120: 7
> unresolved externals
>
> [/code]
>
> And I'm linking with following libraries:
>
> kernel32.lib;user32.lib;gdi32.lib;comdlg32.lib;winspool.lib;
> winmm.lib;shell32.lib;shlwapi.lib;comctl32.lib;ole32.lib;
> oleaut32.lib;uuid.lib;rpcrt4.lib;advapi32.lib;version.lib;
> wsock32.lib;wininet.lib;%(
>
> What am I missing? I am linking with wsock32.lib...
>


I suggest you try building it with the supported method (the msvc build
system in src/tools/msvc), and then inspect the difference.

You can also look up those references in the Microsoft docs (for each API
function there is a listing at the bottom of the page telling you which
header and library is needed). From doing that, AFAICT, you are missing
ws2_32.lib and secur32.lib.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Compatibility of libpg

[Magnus Hagander]

On Thu, May 4, 2017 at 7:32 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 5/4/2017 2:19 AM, Magnus Hagander wrote:
>
> On Wed, May 3, 2017 at 11:31 PM, John R Pierce <pie...@hogranch.com>
> wrote:
>
>> On 5/3/2017 2:20 PM, Magnus Hagander wrote:
>>
>>>
>>> Please note that this method of building libpq has been removed from
>>> Postgres 10, so it's considered to be deprecated for quite some time.
>>>
>>>
>> this page https://www.postgresql.org/docs/current/static/install-windo
>> ws-libpq.html probably should be updated then.
>>
>>
> That page is completely gone in version 10. If you look at
> https://www.postgresql.org/docs/devel/static/install-windows.html you
> will notice the entire 17.2 section has been removed, as well as the text
> on the root page referring to it.
>
>
> well, dropped in the latest not-yet-released version doesn't really make
> it 'deprecated for quite some time'. if this has been the long term
> intention, the docs should have reflected this some revisions back.
>

True. The fact that the reported issue has been around for a very long time
without anybody caring to fix it is though.



> I still think the Windows packagers (EnterpriseDB) should
> have a client-only package which has options to just install the libs, or
> the libs + client utils (psql, pg_dump/restore/dumpall and optionally
> pg_admin).   I realize that this wouldn't be /that/ much smaller than the
> whole 9 yards, but its a psychological thing for the end user, they think
> of the server as 'heavy', also would be good for automated client
> deployments in business envirnoments.
>
>
>
That's a different thing though, and I agree that this would be useful.



-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Compatibility of libpg

[Magnus Hagander]

On Thu, May 4, 2017 at 12:06 AM, Igor Korot <ikoro...@gmail.com> wrote:

>
> On Wed, May 3, 2017 at 5:20 PM, Magnus Hagander <mag...@hagander.net>
> wrote:
> > On Tue, May 2, 2017 at 4:49 AM, Igor Korot <ikoro...@gmail.com> wrote:
> >>
> >> John,
> >>
> >> On Mon, May 1, 2017 at 9:38 PM, John R Pierce <pie...@hogranch.com>
> wrote:
> >> > On 5/1/2017 5:44 PM, Igor Korot wrote:
> >> >>
> >> >>
> >> >> But I want to build from MSVC. I already have a solution for it. All
> I
> >> >> need is to create a project inside that solution which will build the
> >> >> dll
> >> >> and lib files for me.
> >> >>
> >> >> Or I have to use nmake?
> >> >
> >> >
> >> > pretty sure you need to run the top level config script to generate
> all
> >> > the
> >> > right stuff, then you probably can have MSVC run the makefile in the
> >> > libpq
> >> > directory.   I find it easier to just build the whole server, then
> just
> >> > use
> >> > the libpq.dll rather than trying to build pieces seperately, as it
> >> > really
> >> > doesn't take very long.   i believe there are notes on building with
> >> > MSVC on
> >> > Windows,
> >> > https://www.postgresql.org/docs/current/static/install-
> windows-full.html
> >> > ...
> >> > I see there are instructions for building libpq only,
> >> >
> >> > https://www.postgresql.org/docs/current/static/install-
> windows-libpq.html
> >> > but I believe you still need most of the prerequisites as outlined in
> >> > 17.1.1
> >> > ...   17.1.3 discusses a mkvcbuild.pl which makes a pgsql.sln
> project(?)
> >> > for
> >> > Visual Studio
> >>
> >> Ok, I tried to build with nmake as suggested by the official
> >> documentation.
> >>
> >> Two issues:
> >> 1. On the build window I got:
> >>
> >> [quote]
> >> link.exe -lib @C:\Users\Igor\AppData\Local\Temp\nm4696.tmp
> >> rc.exe /l 0x409 /fo".\Release\libpq.res" libpq-dist.rc
> >> Microsoft (R) Windows (R) Resource Compiler Version 6.1.7600.16385
> >> Copyright (C) Microsoft Corporation.  All rights reserved.
> >>
> >> link.exe @C:\Users\Igor\AppData\Local\Temp\nm48F9.tmp
> >>Creating library .\Release\libpqdll.lib and object
> >> .\Release\libpqdll.exp
> >> mt -manifest .\Release\libpq.dll.manifest
> >> -outputresource:.\Release\libp
> >> q.dll;2
> >> Microsoft (R) Manifest Tool version 5.2.3790.2076
> >> Copyright (c) Microsoft Corporation 2005.
> >> All rights reserved.
> >>
> >> .\Release\libpq.dll.manifest : general error c1010070: Failed to load
> and
> >> parse
> >> the manifest. The system cannot find the file specified.
> >> NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft
> >> SDKs\Windows\v7.0A
> >> \bin\mt.EXE"' : return code '0x1f'
> >> Stop.
> >> NMAKE : fatal error U1077: '"c:\Program Files (x86)\Microsoft Visual
> >> Studio 10.0
> >> \VC\BIN\nmake.EXE"' : return code '0x2'
> >> Stop.
> >> [/quote]
> >>
> >> 2. Nevertheless, I did get the libpg.{dll,lib} files.
> >> However, it looks like they are Release mode.
> >
> >
> > Yes, this is a known issue.
>
> Does this mean it will be fixed in the next release?
>

No. It will be removed in the next major release (postgres 10).

It could be fixed in a minor release, but nobody has shown interest in
providing a proper fix for years for it. But if you want to contribute a
fix for it, we could certainly apply it to existing releases, but given
that the method will be gone in version 10 it's doubtful if it's worth it.



> > Please note that this method of building libpq has been removed from
> > Postgres 10, so it's considered to be deprecated for quite some time.
> >
> >
> >>
> >> Is there a way to build a Debug version of the libraries?
> >> I'm building my app in Debug mode right now and prefer not to mix the
> >> libraries.
> >> Especially since I know Debug and Release version with MSVC link to a
> >> different run-time.
> >
> >
> > Yes, using the MSVC build system in src/tools/msvc. See section 17.1.3 on
> > https://www.postgresql.org/docs/current/static/install-windows-full.html
> >
> > (You should be able to build "libpq" as the project - I haven't tested
> it,
> > but I'm pretty sure that should work)
>
> I will need to install ActivePerl? ;-)
>

You will need some level of perl - I believe it works with the upstream
perl as well.


Why not generate the solution and supply them with the source?
>

Because it has to be maintained by people who do not have Visual Studio, or
even Windows. We need it to be auto-generated from the core build system if
there is going to be any chance for it to stay up to date.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Compatibility of libpg

[Magnus Hagander]

On Wed, May 3, 2017 at 11:31 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 5/3/2017 2:20 PM, Magnus Hagander wrote:
>
>>
>> Please note that this method of building libpq has been removed from
>> Postgres 10, so it's considered to be deprecated for quite some time.
>>
>>
> this page https://www.postgresql.org/docs/current/static/install-windo
> ws-libpq.html probably should be updated then.
>
>
That page is completely gone in version 10. If you look at
https://www.postgresql.org/docs/devel/static/install-windows.html you will
notice the entire 17.2 section has been removed, as well as the text on the
root page referring to it.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Compatibility of libpg

[Magnus Hagander]

On Tue, May 2, 2017 at 4:49 AM, Igor Korot <ikoro...@gmail.com> wrote:

> John,
>
> On Mon, May 1, 2017 at 9:38 PM, John R Pierce <pie...@hogranch.com> wrote:
> > On 5/1/2017 5:44 PM, Igor Korot wrote:
> >>
> >>
> >> But I want to build from MSVC. I already have a solution for it. All I
> >> need is to create a project inside that solution which will build the
> dll
> >> and lib files for me.
> >>
> >> Or I have to use nmake?
> >
> >
> > pretty sure you need to run the top level config script to generate all
> the
> > right stuff, then you probably can have MSVC run the makefile in the
> libpq
> > directory.   I find it easier to just build the whole server, then just
> use
> > the libpq.dll rather than trying to build pieces seperately, as it really
> > doesn't take very long.   i believe there are notes on building with
> MSVC on
> > Windows,
> > https://www.postgresql.org/docs/current/static/install-windows-full.html
> ...
> > I see there are instructions for building libpq only,
> > https://www.postgresql.org/docs/current/static/install-
> windows-libpq.html
> > but I believe you still need most of the prerequisites as outlined in
> 17.1.1
> > ...   17.1.3 discusses a mkvcbuild.pl which makes a pgsql.sln
> project(?) for
> > Visual Studio
>
> Ok, I tried to build with nmake as suggested by the official documentation.
>
> Two issues:
> 1. On the build window I got:
>
> [quote]
> link.exe -lib @C:\Users\Igor\AppData\Local\Temp\nm4696.tmp
> rc.exe /l 0x409 /fo".\Release\libpq.res" libpq-dist.rc
> Microsoft (R) Windows (R) Resource Compiler Version 6.1.7600.16385
> Copyright (C) Microsoft Corporation.  All rights reserved.
>
> link.exe @C:\Users\Igor\AppData\Local\Temp\nm48F9.tmp
>Creating library .\Release\libpqdll.lib and object
> .\Release\libpqdll.exp
> mt -manifest .\Release\libpq.dll.manifest
> -outputresource:.\Release\libp
> q.dll;2
> Microsoft (R) Manifest Tool version 5.2.3790.2076
> Copyright (c) Microsoft Corporation 2005.
> All rights reserved.
>
> .\Release\libpq.dll.manifest : general error c1010070: Failed to load and
> parse
> the manifest. The system cannot find the file specified.
> NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft
> SDKs\Windows\v7.0A
> \bin\mt.EXE"' : return code '0x1f'
> Stop.
> NMAKE : fatal error U1077: '"c:\Program Files (x86)\Microsoft Visual
> Studio 10.0
> \VC\BIN\nmake.EXE"' : return code '0x2'
> Stop.
> [/quote]
>
> 2. Nevertheless, I did get the libpg.{dll,lib} files.
> However, it looks like they are Release mode.
>

Yes, this is a known issue.

Please note that this method of building libpq has been removed from
Postgres 10, so it's considered to be deprecated for quite some time.



> Is there a way to build a Debug version of the libraries?
> I'm building my app in Debug mode right now and prefer not to mix the
> libraries.
> Especially since I know Debug and Release version with MSVC link to a
> different run-time.
>

Yes, using the MSVC build system in src/tools/msvc. See section 17.1.3 on
https://www.postgresql.org/docs/current/static/install-windows-full.html

(You should be able to build "libpq" as the project - I haven't tested it,
but I'm pretty sure that should work)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] Recover PostgreSQL database folder data

[Magnus Hagander]

On Sat, Apr 22, 2017 at 3:05 AM, Cat <c...@zip.com.au> wrote:

> On Fri, Apr 21, 2017 at 08:20:38PM -0300, Edson Lidorio wrote:
> > Ls -la /var/lib/pgsql/9.6/data
> >
> > drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
> > drwx--.  4 root root51 Abr 21 06:33 ..
>
> Ensure that the user 'postgres' has permissions to get to
> this dir from / up. This may either mean changing permissions
> on some directories or changing ownership.
>
> More than likely / /var /lib are a permissions thing (likely
> need to be u+rwx,g+rx,o+rx) and /var/lib/pgsql/ and up is an
> ownership thing (postgres:postgres) but this is not guaranteed
> so take care.


Since this is CentOS, I would also look into if it's selinux things that
are incorrect. The easiest way is to turn it off and see if that fixes it
-- if it does, then read up on the selinux docs for how to figure out what
is wrong and probably use restorecon to get things back in order.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

Re: [GENERAL] A change in the Debian install

[Magnus Hagander]

On Thu, Apr 6, 2017 at 3:46 PM, Stephen Frost <sfr...@snowman.net> wrote:

> Tom,
>
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > (But ... these statements are based on an assumption of out-of-the-
> > box Postgres behavior.  I would not exactly put it past the Debian
> > packagers to have decided to change this for reasons of their own,
> > and their track record of telling us about such decisions is many
> > miles south of abysmal.  So you might look at whatever patches
> > are in the Debian package to see if there's anything touching
> > pgstat.c's socket-setup logic.)
>
> I don't believe this is really a fair assessment.  Maybe at some point
> in the distant past, but not today.  Christoph is regularly on this list
> contributing to threads regarding packaging, submitting patches of his
> own for improvements to PG, and the patches currently included in the
> Debian distribution, at least mostly, are for things which really should
> be possible to do with configure options, but which we don't provide
> today, or things we should just be handling already.
>

+1. While this may have been true in a *very* distant past, it's certainly
not anymore. So let's try to avoid spreading disinformation about that.

And FWIW, the RPM distributions have about the same number of patches...



> 51-default-sockets-in-var.patch
>   Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR.  We really
>   should allow this to be changed in configure.
>

This looks exactly like something the RPMs want as well, so we should
definitely look at providing that upstream.

I'll start a discussion with Christoph on if we might, already, be able
> to remove some of these, and where we might be able to make upstream
> changes to remove the need for others.
>

That'd be useful. I think you should also include Devrim to figure out what
things would actually make *both* sides happier.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] browser interface to forums please?

[Magnus Hagander]

On Wed, Apr 5, 2017 at 7:22 PM, Joshua D. Drake <j...@commandprompt.com>
wrote:

> On 03/24/2017 11:45 PM, Tom Lane wrote:
>
>> John R Pierce <pie...@hogranch.com> writes:
>>
>>> On 3/24/2017 9:49 PM, Yuri Budilov wrote:
>>>
>>
> They are uniformly unfriendly when viewed from this end of the
>> relationship.  nabble for instance reposts stuff into the mailing lists
>> that is missing critical portions.  stackoverflow doesn't seem to think
>> they have any responsibility to give back at all.
>>
>
> Stackoverflow gives back by providing an interface people want to use. It
> is free (as in beer) and is hugely popular.
>

I think one of the greatest things that Stackoverflow brins isn't actually
the interface (I for one can't stand it, but I'm clearly not the target
group here), but it's the fact that they have the *userbase* of people. We
have a userbase of "people already using postgres and many of them having
done so for some time because there's a threshold to get over to join this
mailinglist thing". Stackoverflow has a userbase that is orders of
magnitude higher, because they provide a venue for people to ask questions
about *anything* -- so they can use the same venue to ask about their
programming language, their framework-du-jour, their database, their
operating system etc etc.

This is one reason why I don't think having PostgreSQL dedicated web forums
would actually be very interesting today. Those people who prefer to use
the web as their media are more likely to already be using other platforms
which bring them *more value* than a PostgreSQL dedicated forum ever would.
And they don't have to sing up for Yet Another Account. And they can work
on whatever credit-style-kickback their favorite platform does.



> We need to be embracing these external communities because it is where our
> growth is. I run into people every single week that absolutely refuse to
> join these lists. They want nothing to do with email and they have good
> reason.
>


Fully agreed. And I think we're better off doing that than to try to
rebuild our own version of those communities.

Personally, I couldn't stand going through StackOverflow on a regular basis
trying to check if Postgres related questions are answered or not etc.
Luckily, we have other community members who *are* willing to do that, and
they make that platform work. So I'm very grateful for those people doing
it, even better that it's not me.


And also, if somebody wants to take another stab at trying to make web
forums for PostgreSQL, I say let them try. I don't think it would work, but
I'd be happy to be proven wrong.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] browser interface to forums please?

[Magnus Hagander]

On Wed, Apr 5, 2017 at 6:31 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 04/05/2017 09:17 AM, Magnus Hagander wrote:
>
>
>> This has been tried a number of times. I'ts been a couple of years since
>> I last saw one, but multiple people have set up forums, either mirrored
>> or not. They have all died because of either lack of usage or because
>> the person who did it disappeared.
>>
>
> Mostly, because they did not work well and the folks on this end of the
> process had to do more work to get the information necessary to answer the
> question. I know I eventually stopped responding to the questions from
> those sources because it was difficult to follow the information flow.
> Namely you had to crawl back up to the forum to get information and then
> the email thread had mix of information that made it through on its own and
> some subset of information that dedicated people pulled in from the forum.
> That mix depended on dedication level and time available.
>
>
Most likely things like that yes -- which indicates that it really wasn't
enough people who preferred that format to actually reach critical mass.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] browser interface to forums please?

[Magnus Hagander]

On Wed, Apr 5, 2017 at 4:14 PM, vinny <vi...@xs4all.nl> wrote:

> On 2017-04-05 15:11, Vincent Veyron wrote:
>
>> On Tue, 04 Apr 2017 12:01:24 +0200
>> vinny <vi...@xs4all.nl> wrote:
>>
>>>
>>> Every time I tell someone about the mailinglists I then have to explain
>>> how they can subscribe, how to create folders, filters etc. And more
>>> often than not
>>> they just say forget it and go to some forum.
>>>
>>
>> On forums, all you see is the header for the discussion, and the
>> number of messages attached to it.
>>
>> It makes it much more difficult to follow discussions, because you
>> don't know if there are new messages or not, unless you memorized how
>> many were there the last time you looked at it. And even then, you
>> can't tell whether you even read them previously or not, which a
>> mailing list will tell you, because the messages are marked.
>>
>
> It depends entirely on which forum software you use.
> If keeping track of read messages is a requirement then you would obviously
> use a forum that does that for you.
>
> But again, I'm not saying the mailinglist should be replaced by a forum.
> What I'm saying is that many users find forums a lot easier to use and
> give the choice,
> they will opt for the forum. Hence it makes sense to provide something for
> those users,
> if there is the manpower to do so.


This has been tried a number of times. I'ts been a couple of years since I
last saw one, but multiple people have set up forums, either mirrored or
not. They have all died because of either lack of usage or because the
person who did it disappeared.

Not saying it can't be tried again, but in the previous attempts it
certainly hasn't been "many users". It could be that whomever set them up
did a bad job of course, I can't judge that as I didn't personally use them
more than take a look every now and then.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] REFERENCES privilege should not be symmetric (was Re: [GENERAL] Postgres Permissions Article)

[Magnus Hagander]

On Fri, Mar 31, 2017 at 7:40 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Robert Haas <robertmh...@gmail.com> writes:
> > On Fri, Mar 31, 2017 at 11:29 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> >> The argument for not back-patching a bug fix usually boils down to
> >> fear of breaking existing applications, but it's hard to see how
> >> removal of a permission check could break a working application ---
> >> especially when the permission check is as hard to trigger as this one.
> >> How many table owners ever revoke their own REFERENCES permission?
>
> > Sure, but that argument cuts both ways.  If nobody ever does that, who
> > will be helped by back-patching this?
> > I certainly agree that back-patching this change is pretty low risk.
> > I just don't think it has any real benefits.
>
> I think the benefit is reduction of user confusion.  Admittedly, since
> Paul is the first person I can remember ever having complained about it,
> maybe nobody else is confused.
>

I think we also need to be extra careful about changing *security related*
behavior in back branches, even more so than other behavior. In this case I
think it's quite unlikely that it would hit somebody, but the risk is
there. And people generally auto-upgrade to the latest minor releases,
whereas they at least in theory read the top of the release notes when
doing a major upgrade (ok, most people probably don't, but at least some
do).

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Wiki editor privilege

[Magnus Hagander]

On Thu, Mar 23, 2017 at 7:57 AM, Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:

> Hello
>
> I would like to upload the slides of my presentation at Nordic PGDay
> 2017. Could you please grant me edit privilege on the wiki?
>
> My user name is cclavadetscher
>

Hi!

Done.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Magnus Hagander]

On Feb 17, 2017 06:53, "John R Pierce" <pie...@hogranch.com> wrote:

On 2/16/2017 9:43 PM, Magnus Hagander wrote:


>
Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for
you?


he's using HEADER, so cat wouldn't work.he's also using MSDOS/WIndows
style filenames, so cat won't work, anyways..


There are windows equivalents of cat.. But you're right, I didn't think of
the header part.

I'd suggest using something like pgloader, but I don't know if that runs on
MS Windows, either.   its very powerful for doing bulk imports from a wide
range of formats, and even allows data manipulation.


I assume it does if you can figure out how to build it. But I don't think
there are any packages provided for it, so it can be a bit of a challenge.


/Magnus

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Magnus Hagander]

On Fri, Feb 17, 2017 at 6:26 AM, Murtuza Zabuawala <
murtuza.zabuaw...@enterprisedb.com> wrote:

> Hi,
>
> Is there any way to load multiple CSV files at once using single COPY
> command?
>
> I have scenario where I have to load multiple files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there any
> other way to do this using single COPY command?
>
>
Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for
you?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Can't restart Postgres

[Magnus Hagander]

On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas <thoma...@u.washington.edu>
wrote:

> Well that would make more sense of things.  I had removed and re-installed
> the postresql-common package:
>
> https://packages.debian.org/jessie/postgresql-common
>
> and thought that it would leave the main PG package in place.  But perhaps
> I was wrong.  I’ll follow Tom’s advice and just re-install everything
> (saving the old data directory) and hope the new installation can use the
> old data data directory.
>

If you removed it and then installed it, then the removal would remove all
dependent packages and if you then only intalled that one and not the
dependencies that would explain it.

If you had run a reinstall on it, then it would've kept them around.



> One question about this approach though:  the Debian package installation
> automatically initializes the new data directory and starts PG.  If I shut
> it down and copy the old data directory into the newly installed one, will
> there be an xlog issue?
>

You have to copy the xlog along with the database.

Or if you leave it in place where it is, the packages won't initialize a
new data directory.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Can't restart Postgres

[Magnus Hagander]

On Wed, Feb 15, 2017 at 6:03 PM, Shawn Thomas <thoma...@u.washington.edu>
wrote:

> /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory
>
> postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al
>   total 4008
>   drwxr-xr-x 2 root root4096 Feb  9 16:17 .
>   drwxr-xr-x 3 root root4096 Feb  9 16:17 ..
>   -rwxr-xr-x 1 root root   68128 Nov 16 06:53 clusterdb
>   -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
>   -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
>   -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
>   -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
>   -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
>   -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
>   -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
>   -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
>   -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
>   -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
>   -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
>   -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
>   -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
>   -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
>   -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
>   -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb
>
> As I mentioned, this Debian package removes pg_ctl from the bin directory
> and instead attempts to wrap the pg_ctl functionality in a perl script so
> that the PG process is integrated with systemd.  I really wish they hadn’t,
> and it’s part of the reason I’m where I’m at.
>

pg_ctl is normally present in /usr/lib/postgresql//bin on a debian
system. If that is gone, somebody removed it, or you didn't install the
"postgresql-9.4" package which provides it. On a 9.4 system:

$ dpkg -S /usr/lib/postgresql/9.4/bin/pg_ctl
postgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctl

You could try reinstalling the postgresql-9.4 package and see if it comes
back. The rest of the binaries in that directory seems to be from
postgresql-9.4-client though -- have you actually by mistake uninstalled
the server package completely?

As in, that directory is supposed to have the "postgres" binary which is
the database server and it's not there. So there is no wonder it's not
starting...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Can't restart Postgres

[Magnus Hagander]

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake <j...@commandprompt.com>
wrote:

> On 02/14/2017 11:43 AM, Shawn Thomas wrote:
>
>> pangaea:/var/log# systemctl status postgresql
>> ● postgresql.service - PostgreSQL RDBMS
>>Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
>>Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
>>   Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>>  Main PID: 28668 (code=exited, status=0/SUCCESS)
>>CGroup: /system.slice/postgresql.service
>>
>
> What about if use pg_ctl as the postgres user? That will give you a better
> idea.


You don't want ot be doing that on a systemd system, but try a combination
of pg_lsclusters and pg_ctlcluster. Might be you need to shut it down once
that way before it realizes it's down,and then start it back up.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] PostgreSQL corruption

[Magnus Hagander]

On Tue, Feb 14, 2017 at 5:21 AM, James Sewell <james.sew...@jirotech.com>
wrote:

> Hello All,
>
> I am working with a client who is facing issues with database corruption
> after a physical hard power off (the machines are at remote sites, this
> could be a power outage or user error).
>
> They have an environment made up of many of the following consumer grade
> stand alone machines:
>
>- Windows 7 SP1
>- PostgreSQL 9.2.4
>
>
If you're using 9.2.4, you are missing about 4 years worth of bugfixes.
While what you're talking aobut sounds like other issues, you should really
upgrade that to something that doesn't have loads of known bugs and then
re-run the tests.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

[Magnus Hagander]

On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh <andr...@visena.com>
wrote:

> På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian <
> br...@momjian.us>:
>
> On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
> > I would assume that having pg_largeobject in a separate tablespace is
> more and
> > more common these days, having real-cheap SAN vs. fast-SSD for normal
> tables/
> > indexes/wal.
>
> So common that no one has ever asked for this feature before?
>
>
>
> Sometimes one gets the feeling that one is the only one in the universe
> doing something one considers "quite common":-)
>
>
> > So - I'm wondering if we can fund development of pg_upgrade to cope with
> this
> > configuration or somehow motivate to getting this issue fixed?
> >
> > Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
> >
> > Any feedback welcome, thanks.
>
> You would need to get buy-in that that community wants the relocation of
> pg_largeobject to be supported via an SQL command, and at that point
> pg_upgrade would be modified to support that.  It is unlikely pg_upgrade
> is going to be modified to support something that isn't supported at the
> SQL level.  Of course, you can create a custom version of pg_upgrade to
> do that.
>
>
> Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as
> being "at the SQL-level"?
>

Well, it requires that you set allow_system_table_mods on, which is
documented as a developer option. It's documented with things like "The
following parameters are intended for work on the PostgreSQL source code,
and in some cases to assist with recovery of severely damaged databases. There
should be no reason to use them on a production database.".

Perhaps we should add another disclaimer there saying that if you make
changes in this mode, tools like pg_upgrade (or for that matter, pg_dump or
pretty much anything at all) may not work anymore?



> The whole problem seems to come from the fact that BLOBs are stored in
> pg_largeobject which for some reason is implemented as a system-catalogue
> in PG, which imposes all kinds of weird problems, from a DBA-perspective.
>

Yes, there are several issues related to how lo style large objects work.
I've often gone to similar implementations but in userspace on top of
custom tables to work around those.



> Can we pay you at EDB for making such a custom version of pg_upgrade for
> 9.6?
>
>
You're assuming pg_upgrade is the only potential problem. If you are
willing to spend towards it, it would probably be better to spend towards
the "upper layer" problem which would be to make it possible to move
pg_largeobject to a different tablespace *without* turning on
system_table_mods.

That said, I cannot comment to the complexity of either doing that *or*
doing a custom pg_upgrade that would support it. But solving a long-term
problem seems better than solving a one-off one.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Unexpected trouble from pg_basebackup

[Magnus Hagander]

On Wed, Oct 5, 2016 at 3:55 PM, otheus uibk <otheus.u...@gmail.com> wrote:

>
>
> On Tue, Oct 4, 2016 at 10:49 PM, Magnus Hagander <mag...@hagander.net>
> wrote:
>
>>
>>
>> On Tue, Oct 4, 2016 at 10:42 PM, otheus uibk <otheus.u...@gmail.com>
>> wrote:
>>
>>> After a 3 to 4 minute delay, pg_basebackup started doing it's thing and
>>> finished within a few minutes. So now the question is: why the startup
>>> delay?
>>>
>>
>>
>> Sounds to me like it's doing a CHECKPOINT with spreading, which make it
>> take time. Try with "-c fast" and see if the problem goes away.
>>
>
> Maybe not too far off.
>
>
> 2016-10-04 17:43:40.620 GMT 57eb90a0.6e07
> 402 0   LOG:  checkpoint complete: wrote 12799 buffers (1.0%); 0
> transaction log file(s) added, 0 removed, 5 recycled; write=1389.348 s,
> sync=0.033 s, total=1389.400 s; sync files=240, longest=0.003 s,
> average=0.000 s; distance=92915 kB, estimate=129373 kB
> 2016-10-04 18:20:31.714 GMT 57eb90a0.6e07
> 403 0   LOG:  checkpoint starting: time
> 2016-10-04 18:39:19.870 GMT 57eb90a0.6e07
> 404 0   LOG:  checkpoint complete: wrote 10265 buffers (0.8%); 0
> transaction log file(s) added, 0 removed, 4 recycled; write=1128.118 s,
> sync=0.023 s, total=1128.155 s; sync files=190, longest=0.002 s,
> average=0.000 s; distance=73419 kB, estimate=123778 kB
> 2016-10-04 19:07:12.647 GMT [unknown]   pgsync  57f3fde0.52e3   2
>   0   LOG:  replication connection authorized: user=pgsync
> 2016-10-04 19:07:12.703 GMT 57eb90a0.6e07
> 405 0   LOG:  checkpoint starting: force wait
> 2016-10-04 19:20:32.879 GMT [unknown]   pgsync  57f40100.5891   2
>   0   LOG:  replication connection authorized: user=pgsync
> 2016-10-04 19:23:05.249 GMT 57eb90a0.6e07
> 406 0   LOG:  checkpoint complete: wrote 8638 buffers (0.7%); 0
> transaction log file(s) added, 0 removed, 5 recycled; write=952.514 s,
> sync=0.016 s, total=952.546 s; sync files=238, longest=0.002 s,
> average=0.000 s; distance=68257 kB, estimate=118226 kB
> 2016-10-04 19:23:05.249 GMT 57eb90a0.6e07
> 407 0   LOG:  checkpoint starting: force wait
> 2016-10-04 19:28:52.232 GMT 57eb90a0.6e07
> 408 0   LOG:  checkpoint complete: wrote 3102 buffers (0.2%); 0
> transaction log file(s) added, 0 removed, 0 recycled; write=346.957 s,
> sync=0.018 s, total=346.982 s; sync files=149, longest=0.002 s,
> average=0.000 s; distance=36016 kB, estimate=110005 kB
> 2016-10-04 19:30:31.922 GMT [unknown]   pgsync  57f40357.5c70   2
>   0   LOG:  replication connection authorized: user=pgsync
> 2016-10-04 19:30:31.932 GMT 57eb90a0.6e07
> 409 0   LOG:  checkpoint starting: force wait
> 2016-10-04 19:33:40.857 GMT 57eb90a0.6e07
> 410 0   LOG:  checkpoint complete: wrote 1763 buffers (0.1%); 0
> transaction log file(s) added, 0 removed, 1 recycled; write=188.886 s,
> sync=0.030 s, total=188.924 s; sync files=125, longest=0.004 s,
> average=0.000 s; distance=13135 kB, estimate=100318 kB
>
>
>
> OK, so what was happening is that the checkpoints were taking 5 to 15
> minutes minutes, and aborted basebackups were triggering new checkpoints
> which waited on the previous ones.
>
> Is it possible the new environment is not high-performance enough??
>
> possibly relevant configuration change:
>checkpoint_timeout=1h
>
>
>
>
pg_basebackup will always send in a checkpoint. PostgreSQL will time that
to take approximately checkpoint_timeout * checkpoint_completion_target
time. So with checkpoint_timeout set to a very high value, it will by
default target something like 30 minutes before it even gets started. The
only reason it takes as *little* as it does is that your system is lightly
loaded.

You can do a fast checkpoint with the parameter I suggested before, or you
can tune your checkpoint_timeout to be something that gives you a more
reasonable time.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Unexpected trouble from pg_basebackup

[Magnus Hagander]

On Tue, Oct 4, 2016 at 10:42 PM, otheus uibk <otheus.u...@gmail.com> wrote:

> After a 3 to 4 minute delay, pg_basebackup started doing it's thing and
> finished within a few minutes. So now the question is: why the startup
> delay?
>


Sounds to me like it's doing a CHECKPOINT with spreading, which make it
take time. Try with "-c fast" and see if the problem goes away.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Filters not supported for LDAP authentication

[Magnus Hagander]

On Wed, Aug 31, 2016 at 2:43 PM, Bastien Bodart <bastien.bod...@esnah.com>
wrote:

> Hi,
>
> Is there any reason filters are not supported for search+bind LDAP
> authentication?
> There is no option to pass a filter in search+bind mode and
> "ldapsearchattribute" parameter is even checked to prevent filter injection.
> "ldapurl" parameter is defined as an RFC 4516 LDAP URL except filters and
> extensions are not supported despite being parsed.
>

AFAIK there is no reason other than that nobody has gotten around to write
the code for it. I see no reason why we wouldn't accept a patch for that
functionality.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Site down

[Magnus Hagander]

On Thu, Apr 14, 2016 at 4:14 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> Multiple attempts from multiple locations show that the Web site is down
> for me at least. I can ping the address, just not pull up the Web content.
>


Sorry about that. Botched upgrade and the alerts got lost along with the
big issues we've had with ipv6 routing recently.

It should be back up now.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Equivalent of Oracle's per-session statistics (v$client_stats)?

[Magnus Hagander]

On Mar 22, 2016 6:14 PM, "Frits Jalvingh"  wrote:
>
> Hello list,
>
> Oracle has a way to get per-session statistics. You identify a session
using a call to dbms_session.set_identifier('xxx'), then you enable
statistics using dbms_monitor.client_id_stat_enable('xxx').
> After this you do normal database statements.
> Before you close the connection you can read a view, v$client_stats,
which now contains all kinds of metrics specifically to your connection's
use. Metrics that can be read are things like the number of logical blocks
read, physical blocks read etc.
> Using this mechanism you can show exactly how "bad" for instance a screen
from an application behaves, by finding out how much database I/O it does.
>
> I was wondering whether Postgresql has something like this? I looked at
the pg_stats tables but I do not see anything that can be related to the
"current session" or "current connection".
>

There aren't really any on a session basis but there are per transaction.
Take a look at pg_stat_xact_*.

/Magnus

Re: [GENERAL] PostgreSQL crashed, whole PC not responding

[Magnus Hagander]

On Thu, Mar 10, 2016 at 2:37 AM, Kiswono Prayogo <kisw...@gmail.com> wrote:

> Hi, I'm upgrading to PostgreSQL 9.5.1 in 64-bit ArchLinux (Linux
> 4.4.1-2-ARCH) for around 4 days (previously we use 9.4.x), and the
> PostgreSQL crashed and make the whole PC down, with this last log from
> journalctl:
>
> Mar 09 23:31:17 asd kernel: BUG: Bad page state in process postgres
>  pfn:d18515
> Mar 09 23:31:17 asd kernel: page:ea0034614540 count:0 mapcount:0
> mapping:  (null) index:0x1
> Mar 09 23:31:17 asd kernel: flags: 0x2fffe00()
> Mar 09 23:31:17 asd kernel: page dumped because: page still charged to
> cgroup
> Mar 09 23:31:17 asd kernel: page->mem_cgroup:0002
>
> Is there any possible cause of this? or how to search the causation of
> this? since the only log i have is journalctl
> and log_min_duration_statement 2000 (and it shows nothing).
>

That looks like a kernel issue/bug. Both because the log comes from the
kernel, and because PostgeSQL can't actually take down the whole PC as long
as the kernel is working properly.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Email address VERP problems (was RE: [GENERAL] Does a call to a language handler provide a context/session, and somewhere to keep session data?

[Magnus Hagander]

On Wed, Mar 9, 2016 at 7:03 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:

> David Bennett wrote:
> > > ow...@postgresql.org] On Behalf Of Alvaro Herrera
> >
> > > On that subject.  I noticed that Outlook seems to add the "return
> > > path"
> > > addresses (sometimes called bounce address or envelope sender) to the
> > > CC header, which sets a new record in the stupidity scale.  Since we
> > > use VERP, each message gets a different return path address, so with
> > > each reply you make, Outlook adds a new address to the CC.
> >
> > Interesting. I use a lot of mailing lists and I've not run across one
> > actually using VERP before. Is it becoming more frequent?
>
> Not sure if it's becoming more frequent -- I only manage *this* list
> server and we enabled VERP several years ago.  I thought it was common
> practice ... the idea of manually managing addresses that bounce seems
> completely outdated now.
>


It's been frequent for quite some time.



> > I checked the headers. It seems this list is using a VERP address for
> both
> > the Return-path and the Reply-To, and only the Sender identifies the list
> > directly.
>
> I'm pretty sure our list server is not setting the VERP address in
> Reply-To.  That would be insane, wouldn't it.  We don't touch the
> Reply-To header at all.  Maybe some other program along the way modifies
> the email before Outlook gets it?
>

Yeah, same here.

However, if you look at the thread, it seems the VERP address was added to
the *original email*. In the To field. Perhaps that's what confused the MUA
into adding *another* VERP address on the reply?

Then AFAICT in
http://www.postgresql.org/message-id/raw/001601d17852$7bea9e80$73bfdb80$@pfxcorp.com
a second VERP address was added to the mail (very the 485 one).

To me it look slike this was definitely done by the MTA or MUA at
pfxcorp.com. The archived copy (which is delivered the exact same way as a
general email, it doesn't have any shortcut) does not contain this address
naywhere, it was only used as an envelope sender. Possibly it got confused
by the other VERP address in the initial email, which AFAICT is a manual
mistake.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] could not migrate 8.0.13 database with large object data to 9.5.1

[Magnus Hagander]

On Mar 2, 2016 06:01, "John R Pierce"  wrote:
>
> (thread moved from pg_bugs)
> (upgrading a 8.0.13 database on Windows XP 32bit to 9.5.1 on Windows 8 64
bit.)
>
>
> On 3/1/2016 8:05 PM, Premsun Choltanwanich wrote:
>>
>> Modified command by remove -Ft flag as per you suggestion:
>> pg_dump -v -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U
clubadmin -d clubprogram
>>
>> Result (got same message even with parameter -b or not):
>> pg_dump: reading rewrite rules
>> pg_dump: reading policies
>> pg_dump: reading large objects
>> ...
>>
>> pg_dump: creating FUNCTION "public.plpgsql_call_handler()"
>> pg_dump: creating FUNCTION "public.plpgsql_validator(oid)"
>> ...
>>
>> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
>
>
> those all sound like standard postgres functions, its not clear to me why
pg_dump is generating the CREATE FUNCTION code for them.
>
>
>> pg_dump: [archiver] could not write to output file: Invalid argument
>>
>
> ok, presumably your new box has plenty of disk space?  try this on the
new 9.5 system...
>

How large is the total database? The earliest versions of pg on Windows had
bugs in pg_dump for files larger than 2GB. I don't recall exactly when they
were fixed, but this was a long time ago.. Through if my memory is correct
the actual bugs were in pg_dump itself, so using a new pg_dump against the
old server should be safe.

/Magnus

Re: [GENERAL] PostgreSQL flavors

[Magnus Hagander]

On Tue, Feb 23, 2016 at 1:04 PM, Josh berkus <j...@agliodbs.com> wrote:

> On 02/23/2016 07:51 AM, Sherrie Kubis wrote:
>
>> Hello, my first post to the list, thank you for this place to ask
>> questions and get help.
>>
>> Our management has tasked me with devising a plan to migrate our
>> existing databases from Oracle to PostgreSQL.  I’m researching and
>> getting familiar with PostgreSQL before getting a Linux box to start
>> learning and staging.  I have a long way to go, but it will be fun.
>>
>> Out of the gate, I can see different PostgreSQL products – PostgreSQL,
>> PostgreSQLPlus, EnterpriseDB Advanced Server.
>>
>
> So here's a quick rundown.  I'm sure I'm forgetting some, but here's a lot
> of them.  I've deliberately omitted PostgreSQL forks/versions which are no
> longer maintained or not commercially available.
>
> Open Source
> ---
>
> PostgreSQL Plus: EnterpriseDB's distribution of PostgreSQL with extra open
> source tools included in the installer.
>

It's Postgres Plus, not PostgreSQL plus.

And AFAIK, it was also retired some time ago and doesn't actually exist
anymore.

(The advanced servers version does of course, but that's not open source).

I think the only "Plus" product now is their cloud offering?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Let's Do the CoC Right

[Magnus Hagander]

On Fri, Jan 22, 2016 at 7:41 PM, David E. Wheeler <da...@justatheory.com>
wrote:

>
>
> They are in fact both unreconstructed bigots.
>
>

Regardless whether it's true or not (to which I cannot speak), surely
statements like that would violate *both* the contributor covenant *and*
the CoC suggested by others.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Let's Do the CoC Right

[Magnus Hagander]

On Fri, Jan 22, 2016 at 8:37 PM, David E. Wheeler <da...@justatheory.com>
wrote:

> On Jan 22, 2016, at 11:28 AM, Magnus Hagander <mag...@hagander.net> wrote:
>
> > Regardless whether it's true or not (to which I cannot speak), surely
> statements like that would violate *both* the contributor covenant *and*
> the CoC suggested by others.
>
> It may well violate the Contributor Covenant (my apologies, I was out of
> line), but not the current draft of the CoC, IME. Why? Because that’s just
> my opinion, and the CoC draft formally recognizes my right to have an
> “opposing view”.
>

Are you really saying this does not violate "* Participants must ensure
that their language and actions are free
of personal attacks and disparaging personal remarks."?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] dblink connect per socket / specify cluster name

[Magnus Hagander]

On Dec 21, 2015 08:58, "james garner"  wrote:
>
> Dear list,
>
> i'm running PgSQL 9.3 on Debian installed from the Debian packets.
> I'm using dblink to access a different database.
> When im trying to connect to the database by socket via
> dblink_exec('dbname=test host=/var/run/postgresql' ..
> everything works nicely if there's just one db-cluster named 'main'
> I didn't find a way however to select a different cluser name.
> If a connection is made by TCP, the cluster ist determined by the port,
but how is it done when connecting by socket?

It's still determined by the port. Set the host name to the directory where
the sockets are, and the port number will control the actual socket name.

/Magnus

Re: [GENERAL] Support for hardware tokens for server/replication private key

[Magnus Hagander]

On Thu, Dec 3, 2015 at 5:31 AM, mdaswani <m...@quintessencelabs.com> wrote:

> Hi,
>
> Postgres allows client-side SSL requests to use secret keys on hardware
> tokens via OpenSSL engine support. Is there an equivalent way to store the
> server key on a hardware token.
>
> Similarly, is it possible to specify private keys on a hardware token for
> replication connections? Does the sslkey parameter of the primary_conninfo
> string in the recovery.conf file accept an OpenSSL Engine token key?
>

While I haven't tested it and haven't heard of anybody else who has, it
should work. From a libpq perspective ,the replication standby is "just
another client", so any parameters that work for libpq should work there.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

[Magnus Hagander]

On Nov 29, 2015 18:34, "George Neuner"  wrote:
>
> On Sun, 29 Nov 2015 05:04:42 -0500, "Steve Petrie, P.Eng."
>  wrote:
>
> >"George Neuner"  wrote in message
> >news:kaed5btl92qr4v8ndevlgtv0f28qaae...@4ax.com...
> >
> >> My vote for an email client would be Thunderbird.  It runs on XP or
> >> higher and you can import Outlook's PST files so as to keep your mail
> >> archives.  Importing PST files directly requires Outlook be available
> >> on the same system [there is also a less friendly way to do it via EML
> >> files exported from Outlook where Outlook is not on the same system].
> >>
> >
> >It's a common misconception that MS Outlook Express is compatible with MS
> >Outlook. But in fact the two products are architecturally unrelated.
>
> My understanding was that OE was based on the old (Win9x) Outlook.  I
> know it isn't the same as the "enterprise" version.

This is fantastically of topic but no, it was not. OE was based on the old
"Internet mail and news".  The actual outlook product has always been
separate.

/Magnus

Re: [GENERAL] 9.3.9 ?

[Magnus Hagander]

On Jun 12, 2015 1:33 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote:

 Birta Levente wrote:
  In the postgresql yum repo appeared the 9.3.9 and 9.4.4, but on the
  postgresql.org nothing about the new version. Where I can see the
changelog?

 AFAIK, it is being packaged and will be announced soon.

The rpm packages were accidentally released a bit early. The announcement
will be later today. But they have the correct content, so they are safe to
use. Release notes will be available alongside the announcement.

/Magnus

Re: [GENERAL] count distinct and group by

[Magnus Hagander]

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz mabew...@gmail.com wrote:

 Hi,
 I'm not sure why there is a reason for such behaviour.

 For this table:

 create table bg(id serial primary key, t text);

 This works:

 select count(id) from bg;

 This works:

 select count(distinct id) from bg;

 And this doesn't:

 select count(distinct id) from bg order by id;
 ERROR:  column bg.id must appear in the GROUP BY clause or be used in
 an aggregate function
 LINE 1: select count(distinct id) from bg order by id;


There is no id column in the returned dataset to order by. You are just
returning one value, how would it be ordered? (and that row has a column
named count - but you can alias it to SELECT count(distinct id) AS id
FROM bg ORDER BY id - it just makes no sense to order a single row..

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Upgrading hot standbys

[Magnus Hagander]

On Wed, Apr 29, 2015 at 6:19 AM, Aaron Burnett 
aaron.burn...@us.dunnhumby.com wrote:


  Greetings,


  I'm in the process of upgrading PG 9.1.3 to 9.4.1 in the near future.


  I have several machines which each house unique databases. Each of those
 are replicated to a standby server with matching configurations. A total of
 10 servers, 5 masters, 5 slaves. Everything runs on Ubuntu.


  My question, as I can't seem to find any documentation on this part, is
 once I successfully upgrade the master I will need to upgrade the standby
 as well. Will I have to rebuild the standby from scratch, or will the
 standby pick up where it was before the upgrade if I do things correctly?



You upgrade the master, and then you rebuild the standbys from a new
basebackup (using pg_basebackup or manually with start/stop backups etc).
You can't upgrade the standbys and have them re-join the master, they have
to be redone from scratch.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] GiST indeices on range types

[Magnus Hagander]

On Sat, Mar 28, 2015 at 7:52 AM, Rebecca Zahra rebeccaza...@gmail.com
wrote:

 Good morning,

 I am Rebecca Zahra and I am currently in my final year of Masters studies
 at the University of Malta. My thesis is about the usage of indexes for
 multi-dimensional data.

 I was going through the posts regarding GIST indexes and I came across the
 following
 http://dba.stackexchange.com/questions/39589/optimizing-queries-on-a-range-of-timestamps-two-columns

 I was wondering if maybe you can help me with a question.  I know that an
 R-Tree index implementation is used on top of GIST to index spatial data.
 Can you please tell me what type of index is used on top of GIST to index 
 *range
 types*?


PostgreSQL has had indexable range types for quite some time now:
http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-GIST

Indexable with gist or spgist. I don't think the docs cover the actual
implementation internals though - you'll probably have to go to the source
if you need that.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Ransomware article

[Magnus Hagander]

On Tue, Feb 3, 2015 at 3:33 PM, Gordon Haverland 
ghave...@materialisations.com wrote:

 TheRegister is running an article about someone breaking into a dbase,
 taking control of the encryption key, and 6 or so months later
 demanding ransom from the owner of the dbase.


 http://www.theregister.co.uk/2015/02/03/web_ransomware_scum_now_lay_waste_to_your_backups/

 Anyone want to comment on this?  In general, or PostgreSQL in
 particular?



As far as I can tell from that article they never broke into any database.
They broke into the PHP server, using bugs in PHPBB and injected encryption
code in the PHP application so that all data was encrypted *before* it was
sent to the database. The database itself was not compromised, it just
 stored the encrypted texts as the application asked it to.

This would obviously break things as simple as a LIKE matching on a string,
but if the application is trivial enough not to use that, it's certainly
doable. But all the security issues are 100% within the application itself.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] sslmode verify-ca and verify-full: essentialy the same?

[Magnus Hagander]

On Tue, Jan 27, 2015 at 2:29 PM, David Guyot 
david.gu...@europecamions-interactive.com wrote:

 Hi, there.

 Firstly, as this is my first post on a PgSQL ML, I hope this ML is the
 good one for my question.

 I'm trying to secure further some PgSQL servers and am reading
 documentation about libpq sslmode option. I have a question about that:
 as I understand the internals of this option, the difference between
 verify-ca and verify-full is that, for verify-full, client will compare
 the hostname the server gave and the one in the SSL certificate, and
 will give up if these two values differ. Am I right up to now?


Almost correct. It will compare the hostname that the client used (in the
connection string) with the hostname in the SSL certificate, and give up if
the two values differ.

The server does not give the client a hostname at any point (other than the
CN of the certificate).


If I'm right, I feel like the extra security of verify-full compared to
 verify-ca is merely a smoke screen because, as far as I know, nothing
 prevents a crafted server to read the certificate's hostname and give
 this one as its own, and the libpq shouldn't show a better MitM
 protection with verify-full than with verify-ca. If I'm wrong, where am
 I wrong? How does libpq verify the server's name? Reverse DNS? Other
 mean?


libpq uses the hostname that you specify in the connection string (or in an
environment variable, or however you end up specifying it).


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] sslcompression / PGSSLCOMPRESSION not behaving as documented?

[Magnus Hagander]

On Fri, Jan 16, 2015 at 8:41 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:

 On 01/16/2015 08:30 AM, Tom Lane wrote:

 Maciek Sakrejda mac...@heroku.com writes:

 I'm having a hard time getting SSL compression working (or even figuring
 out why it's not working) with my local Postgres server. The setting [1]
 is
 documented to default to on, but according to the banner when I connect
 with psql, it's off.


 Possibly you have the same type of problem mentioned here:

 http://www.postgresql.org/message-id/CABUevEytxEQtbMeuKpJ8tYjeeB37m
 zdq7baszezn6egcgrd...@mail.gmail.com


 Yes that would seem to be the issue:

 https://launchpad.net/ubuntu/trusty/+source/openssl/+changelog

 openssl (1.0.1e-3ubuntu1)

 Disable compression to avoid CRIME systemwide (CVE-2012-4929).



 although Ubuntu may well have done it a bit differently than Red Hat,
 ie the way to override openssl's default behavior might be different.

 regards, tom lane




There's been a few reports on this now. Perhaps we should add a note to the
docs (not necessarily saying how to fix it, as it may differ, but a note
saying that many distributions changed the way this is handled and that you
might need to set an external override)?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7

[Magnus Hagander]

On Mon, Nov 10, 2014 at 1:45 AM, David Johnston
david.g.johns...@gmail.com wrote:
 Yes, that is what I was referring to.  The Nabble.com website showed them.

 http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-td5826230.html

Then it's more logical. Nabble simply did not include that when the
email was posted. I would call that either a bug or at least a
limitation in Nabble - I think only they can figure out what's wrong
and if it's fixable. Until then, we'll have to recommend people just
post plaintext from there (which seems to work) or use a more capable
MUA.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] SSL Certificates in Postgres 9.3 and Windows 7

[Magnus Hagander]

On Mon, Nov 10, 2014 at 8:53 PM, John R Pierce pie...@hogranch.com wrote:
 On 11/10/2014 11:13 AM, Magnus Hagander wrote:

 Then it's more logical. Nabble simply did not include that when the
 email was posted. I would call that either a bug or at least a
 limitation in Nabble - I think only they can figure out what's wrong
 and if it's fixable. Until then, we'll have to recommend people just
 post plaintext from there (which seems to work) or use a more capable
 MUA.


 I suspect its the centos mail list server thats stripping attachments.  I
 know most of my personal email lists do so.

The post was made to the pgsql-general list AFAICT, and I'm pretty
sure it doesn't strip any attachments. I would be hard to deal with
patches if it did...


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] SSL Certificates in Postgres 9.3 and Windows 7

[Magnus Hagander]

On Sun, Nov 9, 2014 at 11:37 PM, David Johnston
david.g.johns...@gmail.com wrote:


 On Sunday, November 9, 2014, Adrian Klaver adrian.kla...@aklaver.com
 wrote:

 On 11/09/2014 10:14 AM, David G Johnston wrote:

 Adrian Klaver-4 wrote

 Thank you for all comments and suggestions.


 More comments/suggestions will have to wait until the missing pieces are
 filled in.


 I read most of these mailing list emails via Nabble and the pieces you
 show
 as missing are present in what I am reading.  If I go to reply and quote
 the
 original message the missing sections are sour rounded by raw tags.


 Hmm, is there a way to make Nabble aware of this and fix it?


 Looking at the official mailing list archive these sections are missing
 there.




 --
 Adrian Klaver
 adrian.kla...@aklaver.com


 I don't know how the OP sent the original e-mail but since I could read the
 problem areas the question is why other e-mail clients aren't seeing them...

I'd be more interested in how *you* could see them - unless you are
just referring to seeing them on nabble.com?

The original as delivered through the mailinglist is in it's raw form
at 
http://www.postgresql.org/message-id/raw/1415506067738-5826230.p...@n5.nabble.com
- which does not contain those parts. And it wasn't event sent as
multipart, so there is not much of ways to misparse it.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


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

[Magnus Hagander]

On Thu, Oct 30, 2014 at 8:31 PM, Stephen Frost sfr...@snowman.net wrote:
 * Adrian Klaver (adrian.kla...@aklaver.com) wrote:
 On 10/30/2014 12:02 PM, Torsten Förtsch wrote:
 I don't know if this is the right mailing list for my question but I
 have already emailed pla...@postgresql.org without any response.

 That's the correct place to ask.  -general is not.

 So, what did I wrong?

 Probably nothing, I would say you are waiting on step 4 from below
 to happen.

 http://planet.postgresql.org/add.html

 Indeed.  The process is not automated and the moderators get to the blog
 approval when they get a chance to.  Admittedly, it's usually faster,
 but it'll get done.

I think in this particular case, much of the blame can be placed on
everybody being at pgconf.eu last week, and struggling under
backlogs. But as Stephen says, the backlog will eventually get worked
through, and the moderation happen.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Where should I post 3rd party product announcements?

[Magnus Hagander]

On Tue, Oct 14, 2014 at 11:42 AM, Jonathan Neve jonat...@copycat.fr wrote:
 Hello Amit,

 I tried pg-announce as well, but my message didn't go through…

Not having seen the actual post, it may have been moderated based on
the rules at https://wiki.postgresql.org/wiki/NewsEventsApproval.
These are primarily for website news postings - but a website news
postign is really what you should do with it, rather than post it to a
mailinglist. So I suggest a look at those rules and then a website
news submission that follows them.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Where should I post 3rd party product announcements?

[Magnus Hagander]

It's on http://www.postgresql.org/, click the link that says Submit news.

//Magnus

On Tue, Oct 14, 2014 at 11:55 AM, Jonathan Neve jonat...@copycat.fr wrote:
 Sorry for being a bit dense, but where should I submit a website news
 posting ?

 Le mardi 14 octobre 2014 11:45:55, Magnus Hagander a écrit :

 On Tue, Oct 14, 2014 at 11:42 AM, Jonathan Neve jonat...@copycat.fr
 wrote:

 Hello Amit,

 I tried pg-announce as well, but my message didn't go through…


 Not having seen the actual post, it may have been moderated based on
 the rules at https://wiki.postgresql.org/wiki/NewsEventsApproval.
 These are primarily for website news postings - but a website news
 postign is really what you should do with it, rather than post it to a
 mailinglist. So I suggest a look at those rules and then a website
 news submission that follows them.






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



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


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

[Magnus Hagander]

On Fri, Jun 13, 2014 at 5:25 AM, Saravanan Subramaniyan sara1...@gmail.com
wrote:

 Thanks Magnus. We have removed as well as replaced the OpenSSLlibraries.
 The postgresql service is not coming up (SSL is turned off). I thought
 OpenSSL is used when we turn on SSL in postgresql.



PostgreSQL *uses* OpenSSL, but does not contain it.

PostgreSQL is still linked against openssl, so if you replaced it with an
incompatible version then it would break. But as I said, it depends on your
distribution of PostgreSQL. As long as you use something like RPM or DEB
packaging, that's all taken care of by the operating system and nothing is
bundled by PostgreSQL. If you installed manually from source, for example,
then of course you need to make sure that your updated openssl is
compatible with the old one.

//Magnus


 Thanks
 V.S.Saravanan


 On Thu, Jun 12, 2014 at 7:56 PM, Magnus Hagander mag...@hagander.net
 wrote:

 On Thu, Jun 12, 2014 at 8:43 AM, Saravanan Subramaniyan 
 sara1...@gmail.com wrote:

 Hi All,
   Recently OpenSSL released Security Advisory. Please refer below link

  http://www.openssl.org/news/secadv_20140605.txt.

 We are using postgresql version 9.2.8 which is vulnerable. Is postgresql
 planning to release new version which include OpenSSL 1.0.1h?


  PostgreSQL itself is not vulnerable, so we will not release a new
 version.

 If you are using the EnterpriseDB graphical installers, they are indeed
 bundling the OpenSSL and it at least used to be the vulnerable version.
 Unfortunately they don't seem to have information about the updates yet - I
 will see if i can ping them about making sure that goes on there. I think
 they have already patched it - but it's not confirmed on the website.

 --
  Magnus Hagander
  Me: http://www.hagander.net/
  Work: http://www.redpill-linpro.com/





-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] OpenSSL Vulnerabilities

[Magnus Hagander]

On Thu, Jun 12, 2014 at 8:43 AM, Saravanan Subramaniyan sara1...@gmail.com
wrote:

 Hi All,
   Recently OpenSSL released Security Advisory. Please refer below link

 http://www.openssl.org/news/secadv_20140605.txt.

 We are using postgresql version 9.2.8 which is vulnerable. Is postgresql
 planning to release new version which include OpenSSL 1.0.1h?


PostgreSQL itself is not vulnerable, so we will not release a new version.

If you are using the EnterpriseDB graphical installers, they are indeed
bundling the OpenSSL and it at least used to be the vulnerable version.
Unfortunately they don't seem to have information about the updates yet - I
will see if i can ping them about making sure that goes on there. I think
they have already patched it - but it's not confirmed on the website.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Heartbleed Impact

[Magnus Hagander]

On Thu, Jun 5, 2014 at 7:30 PM, Dev Kumkar devdas.kum...@gmail.com wrote:


 On Wed, Apr 16, 2014 at 9:08 PM, Dev Kumkar devdas.kum...@gmail.com
 wrote:

 On Wed, Apr 16, 2014 at 7:50 PM, Stephen Frost sfr...@snowman.net
 wrote:

 * Dev Kumkar (devdas.kum...@gmail.com) wrote:
  I just downloaded the latest binaries from EnterpriseDB and when
 checked
  with libssl.so.1.0.0 can see this:
  OpenSSL 1.0.1g 7 Apr 2014
 
  OpenSSL 1.0.1g is the patched version.

 Yes, checked w/ them and they say it's all patched..

  Awaiting confirmation and also please let know if there is certain
 NOTE or
  link which talks about this fix from EnterpriseDB side.

 There's a note on the 'installers' page here:
 http://www.enterprisedb.com/products-services-training/pgdownload

 I believe they're going to add a note to the other page too.

 Thanks,

 Stephen


 Thanks for the confirmation. Yup checked the NOTE on 'installers' page
 and a note on binary page will really help.

 Regards...


 Hello Guys,

 For postgreSQL, is there any OpenSSL fix coming up for this issue:
 http://www.zdnet.com/openssl-fixes-another-severe-vulnerability-730253/

 Currently in PostgreSQL 9.4.3 the version is as follows: OpenSSL 1.0.1g 7
 Apr 2014

 As per the above link, fixed OpenSSL version would be 1.0.1h

 Looking forward for some comments here.


Hi!

The guys at EnterpriseDB are busy building new installers as we speak, I
would expect them to be out tomorrow or so.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Online recovery of Tablespace

[Magnus Hagander]

On Thu, May 22, 2014 at 2:41 AM, Sameer Kumar sameer.ku...@ashnik.comwrote:

 Hi,

 I am designing backup strategy for a PostgreSQL database (v9.3). I have a
 scenario for recovery of tablespaces:

 1. Backup of whole database (including individual tablespaces which are
 stored on different disks) has been taken at 11AM

 2. My disk which stores tablespace- tblspc1 crashed at 2:00PM

 3. Can I restore the backup of 11AM (only for one tablespace) and then
 recover that tablespace to 2:00PM state?


 Is this possible? I have attached the steps I tried (I believe logically
 my steps are wrong, since I am using recovery.conf but I am not replacing
 data directory).

 But is there any way to specify in recovery.conf or otherwise that I would
 allow me to do recovery of transactions of a particular tablespace? A
 custom solution which occurs to me is using pg_xlogdump contrib. Has anyone
 tried something similar?


We do not support recovering just one tablespace. You have to recover the
whole database.

(I see that you are using EnterpriseDB's propietary version based on the
paths, but I don't believe they have made any such extensions either)


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] LDAP authentication not working

[Magnus Hagander]

On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel sfa...@hawaii.edu wrote:

 I don't think SSL support for LDAP is supported. Have you tried TLS on
 port 389?


Correct, and you need to set ldaptls=1 to use that as well.

(And yes, unfortunately the LDAP error messages from openldap are
notoriously bad)

//Magnus



 On May 13, 2014 8:20 PM, Jürgen Fuchsberger 
 juergen.fuchsber...@uni-graz.at wrote:

 Hi,

 I'm running postgresql 9.1 on Debian and am trying to set up LDAP
 authentication using the following configuration in pg_hba.conf:

 hostssl testdb  all 143.50.203.0/24 ldap ldapserver=
 wegc24.uni-graz.at
 ldapport=636 ldapbinddn=cn=nss,dc=uni-graz,dc=at
 ldapbindpasswd=thepasswd ldapbasedn=dc=uni-graz,dc=at


 Trying to access testdb via psql fails with the following error in the
 log:
 '''could not perform initial LDAP bind for ldapbinddn
 cn=nss,dc=uni-graz,dc=at on server wegc24.uni-graz.at: error code
 -1'''

 Unfortunately I did not find what error code -1 means.

 Ldapsearch works fine:
  ldapsearch -W -H ldaps://wegc24.uni-graz.at:636/ -D
 CN=nss,DC=uni-graz,DC=at

 Interesting is also, that postgres seems to not even reach the ldap
 server: If I change parameter ldapserver to a non-existing url it gives
 the same error code -1.

 Any help much appreciated!

 Best,
 Juergen




-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] LDAP authentication not working

[Magnus Hagander]

On Wed, May 14, 2014 at 11:48 AM, Jürgen Fuchsberger 
juergen.fuchsber...@uni-graz.at wrote:



 On 05/14/2014 09:10 AM, Magnus Hagander wrote:
  On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel sfa...@hawaii.edu
  mailto:sfa...@hawaii.edu wrote:
 
  I don't think SSL support for LDAP is supported. Have you tried TLS
  on port 389?
 
 Thanks for the hint, no wonder it does not work. Unfortunately this info
 is not in the postgres documentation.


It is - indirectly, in the ldapurl documentation. To use encrypted LDAP
connections, the ldaptls option has to be used in addition to ldapurl. The
ldaps URL scheme (direct SSL connection) is not supported.

But maybe it could be made more clear...


  Correct, and you need to set ldaptls=1 to use that as well.

 This does not work with our LDAP server (seems it is not configured to
 support TLS)


That's strangely configured. The LDAP TLS support (in the protocol) is the
standardized one, and the SSL wrapper  mode is not in the standard.

I *think* the SSL wrapper really is just that - wrap it in a standard SSL
connection. In which case it might work if you set up stunnel or something
like that to proxy the connection for you.



 Any idea whether LDAP over SSL will be supported in future postgres
 releases?


I am not aware of any such plans, but if you (or somebody else) is willing
to write a patch, I don't see a reason it would be rejected. Even though
it's non-standard, it's fairly widespread. I recall there being a reason it
wasn't added in the first place, but I don't recall what it was.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Monitoring Pg servers with Microsoft SCOM

[Magnus Hagander]

On Mon, May 5, 2014 at 3:15 AM, Glen Eustace geust...@godzone.net.nzwrote:


 On 5/05/2014, at 12:26 pm, Adrian Klaver adrian.kla...@aklaver.com
 wrote:

  On 05/04/2014 04:17 PM, Glen Eustace wrote:
  I am in the process of deploying Microsoft System Centre Operations
  Manager and was hoping that somebody had either developed or knew of
  where I could get hold of a management pack for PostgreSQL.
 
  I am not sure whether there is an instrumentation interface into the DB
  so haven't yet looked at rolling our own.
 
  Any comments appreciated.
 
  Searches on this all seem to point back to this post:
 
 
 http://blogs.technet.com/b/kevinholman/archive/2012/03/19/opsmgr-how-to-monitor-non-microsoft-sql-databases-in-scom-an-example-using-postgre-sql.aspx

 Yes, I had seen that.  I was more interested in being able to instrument
 the Pg internals, connection counts, transaction rates, RAM usage etc.  In
 a similar sort of way to MS-SQL.

 Running transactions against an individual DB is still useful but I was
 hoping to do better.


As long as you can run arbitrary SQL, you can get all the information out.
Assuming it can do something with it rather than just run a plain query. If
so, I suggest you take a look at the check_postgres nagios plugin or the
munin plugins for some examples of which SQL to run to get the interesting
metrics back that you want. Some quick googling shows several examples of
how to monitor with custom SQL queries, but I don't know enough (or
anything) about SCOM to recommend any of them in particular.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] UNIQUE contraint that is initially deferred in Postgres 8.4.13

[Magnus Hagander]

On Thu, Mar 13, 2014 at 12:59 PM, Johannes Bauer dfnsonfsdu...@gmx.dewrote:

 Hi list,

 I'm having trouble with a UNIQUE constraint that I need to have
 DEFERRABLE INITIALLY DEFERRED. On my Dev machine (Postgres 9.1) it works
 fine:

 alter table foo drop constraint bar;
 ALTER TABLE

 alter table foo add constraint bar UNIQUE (col1, col2) deferrable
 initially deferred;
 ALTER TABLE


 In Production (Postgres 8.4.13) this blows up in my face although it
 should be supported according to the docs:


 alter table foo drop constraint bar;
 ALTER TABLE

 alter table foo add constraint bar UNIQUE (col1, col2) deferrable
 initially deferred;
 FEHLER:  Syntaxfehler bei »DEFERRABLE«
 LINE 1: bar UNIQUE (col1, col2) DEFERRABLE...
 ^

 (i.e. syntax error at DEFERRABLE, psql seems to ignore my locale
 setting).

 Does somebody know what the reason for this could be? I'm kind of puzzled.


My guess is you're looking in the wrong place in the docs - can you specify
where you are looking? If you are in the right place then the docs are
wrong.

Deferrable unique constraints are a new feature in PostgreSQL 9.0, so it
shouldn't be working in 8.4.

(And as a sidenote, a reminder that 8.4 will go end of life in just a
couple of months, so if you aren't already planning an upgrade of your
production environment, it's probably a good idea to start doing that, see
http://www.postgresql.org/support/versioning/)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] High Level Committers Wanted

[Magnus Hagander]

On Wed, Mar 12, 2014 at 4:19 PM, bobJobS russelljan...@yahoo.com wrote:

 I am currently working for the Government and there is a huge push to move
 our Oracle databases to a FOSS database solution. Right now, PostgreSQL is
 high on the list.

 What we need is a meeting/training session with the main contributors to
 Postgres to help facilitate solutions of some of the Oracle functionality
 to
 Postgres. This may be an opportunity to gather Government requirements for
 additional Postgres releases. The meeting would be in the Columbia area.

 Some of our needs:
 Package structure similar to Oracle
 Parallel queries
 Multi Master Replication

 Some of the names I've seen
 Tom Lane
 Robert Haas
 Greg Smith
 Simon Riggs

 Please let me know if a meeting is possible. There is another in-house
 meeting April 17th where I would like to pass on contact information or
 needs from the contributors.



I would suggest you consider attending pgcon, as many of the major
developers will be there, including many committers (both those you have
mentioned and others). I'm sure several of these topics will already be
covered there, and there's also an unconference day that is open for any
topic.

See http://www.pgcon.org/2014/.

No, it's not in the Columbia area (any of them), but on the other hand,
there will be experts from all over the world there.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] stand by is starting until I do some work in the primary

[Magnus Hagander]

On Sun, Feb 23, 2014 at 8:25 AM, Jov am...@amutu.com wrote:

 Sure.Before the restart,I have login in the slave many times.
 some more info ,but I don't think related:
 centOS 6 ,amd64,128GB memory,24 core, pgsql-9.3.3,master and slave in the
 same host.

 this is my first instance of 9.3，I don't exprence this before on 9.0~9.2.



It will probably work again after a CHECKPOINT (manual or automatic), if
there is *some* activity on the master. You may be experiencing what the
patch ed46758381ff63a422fcb0b1f2763eb13273640f, which will be included in
9.4. But the workaround is to make sure something happens on the master and
do a checkpoint.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Is pgFoundry Down? (2/18/2014)

[Magnus Hagander]

On Tue, Feb 18, 2014 at 6:07 PM, Purdon kylepur...@gmail.com wrote:

 It seems that pgfoundry.org is down? Is this the case for everyone?


According to http://www.downforeveryoneorjustme.com/pgfoundry.org it is,
yeah.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] CET ERROR: requested WAL segment has already been removed

[Magnus Hagander]

On Fri, Feb 14, 2014 at 10:08 AM, Martin Terjan mar...@terjan.de wrote:

 Dear all,

 Using Postgresql 9.3. on Windows x64.

 I had streaming replication set up from master to slave, but the slave
 shut down and became out of sync with the master. So... I tried to set it
 back up, without success.

 I then checked the *master* log and, although the server seems to be
 working, the log is full with
 CET ERROR: requested WAL segment 00010009008E has already
 been removed.


Note that the CET part of that message is actually the timezone of the
timestamp of the log message, and not part of the actual error :)


What should I do? While I have this error replication fails, because the
 slave looks for the same (missing) WAL segment. The master seems to be
 working, but of course it's possible that I've lost data somewhere.


If you have log archiving set up, just configure your slave so it can
access the log archive (as if restoring from a pitr backup - just add a
restore_command to recovery.conf).

If you don't have log archiving set up, you must rebuild the slave when it
has fallen so far behind that it can't catch up anymore. So a new base
backup of it.

If you don't have a log archive, you should probably also set
wal_keep_segments to a higher value to decrease the risk of it happening
again.


Is there anything sensible I can do beyond restoring from the last good
 backup?


For the slave, yes. The master is perfectly fine, so you don't need to do
anything about that one. The errors are listed in the master log because
they are caused by commands sent from the slave, but they do not indicate a
problem on the master.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_basebackup and checkpoints

[Magnus Hagander]

On Wed, Feb 5, 2014 at 9:15 AM, Alan Nilsson anils...@apple.com wrote:

 I tried (and finally succeeded) to do a base backup tonight using
 pg_basebackup.  I was backing up a server that had no activity on it.  The
 backup connected but no data was transferring, both ends sitting idle.   It
 finally occurred to me to try a manual checkpoint and it then the backup
 took off and completed.

 According to 24.3 in the docs, pg_start_backup() performs a checkpoint.
  So I was surpassed that using the pg_baseback utility I had to manual kick
 a checkpoint before the transfer would start.

 Is this correct behavior?


The checkpoint that's started by both pg_basebackup and pg_start_backup()
is by default a spread checkpoint, which means we intentionally try to
make it take longer, up to several minutes (depending on the value of
checkpoint_completion_target). This is normally a good idea on a busy
system, but can lead to these interesting results on those that are
almost idle.

Running a manual CHECKPOINT always runs it in fast mode, which is
checkpoint as quickly as possible, ignoring the setting of the completion
target.

My guess is you just didn't wait long enough (typically
checkpoint_completion_target * checkpoint_timeout or somewhere in that
region) - if you had, it would've started the backup for you.

If you know your system is going to be mostly idle, you can instruct
pg_basebackup to issue a fast checkpoint instead of the default one. Just
add -c fast to the commandline options. There should never be a need to
run a manual checkpoint.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_basebackup failing

[Magnus Hagander]

On Jan 15, 2014 12:07 PM, Sameer Kumar sameer.ku...@ashnik.com wrote:

 The error you are seeing is triggered because this relation file
 exceeds MAX_TAR_MEMBER_FILELEN or 8GB for a single tar member, which
 is as well the norm for tar.


 I thought PostgreSQL would break the file if it grows beyond 1GB (1GB is
segment size which one can modify while compiling). Am I missing something
or have I got it totally wrong?


Yeah, how did you actually end up with a file that size? Do you have any
non standard compiler options set when you built your server?

/Magnus

Re: [GENERAL] pg_stop_backup running for 10h?

[Magnus Hagander]

On Sat, Jan 11, 2014 at 11:26 PM, François Beausoleil
franc...@teksol.infowrote:

 Hi all,

 I'm using OmniPITR to build a new slave. According to pg_stat_activity,
 pg_stop_backup has been running for nearly 11 hours. The WAL archive
 command is running just fine and reporting Segment X successfully sent to
 all destinations.

 I had the same issue almost a year ago (
 http://www.postgresql.org/message-id/9cc57302-10f8-4678-bbd3-028ec6b57...@teksol.info),
 but don't have permission issues this time around.

 What could cause a pg_stop_backup() to run for such a long time?


Can't speak for the OmniPITR specific parts, but typically the
archive_command reacting strangely would cause pg_stop_backup() to wait.

You include the logs from omniptr, but do you get anything in the
*postgresql* logs? If it's the archive command it should clearly tell you
that. It should also tell you if you can safely cancel the pg_stop_backup()
command.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] postgresql-common breaks logrotate

[Magnus Hagander]

On Fri, Dec 27, 2013 at 12:11 AM, Steve Crawford 
scrawf...@pinpointresearch.com wrote:

 People who have attempted to install PGDG versions of PostgreSQL on recent
 Ubuntu releases have run into the cascading problem of postgresql wanting
 to destroy Ubuntu.

 Based on the packaging:
 postgresql depends on postgresql-common
 postgresql-common breaks logrotate (3.8)
 ubuntu-standard depends on logrotate
 profit?...

 It appears the reason for the claimed breakage is a very slight update to
 logrotate that requires one to tell logrotate about files/directories with
 non-standard ownership/permissions. Getting logrotate to stop complaining
 is trivially resolved by the user by adding a single su directive to the
 /etc/logrotate.d/postgresql-common or by having PostgreSQL log to syslog.

 It seems that breaks is overkill and the hassle imposed by that
 declaration far exceeds any benefit therefrom


This is a known issue documented in the FAQ:
http://wiki.postgresql.org/wiki/Apt/FAQ#I_am_using_a_non-LTS_release_of_Ubuntu

The apt repository only really provides packages for LTS versions - it just
usually happens to work on other releases as well. I believe the concern
here is not to risk breaking things for the LTS users - we don't want to
change the logging defaults in a minor patch upgrade.

That said, hopefully it'll get fixed at least by the next release, since
that's going to be another LTS. There's been talk about changing the way
the logging is done in the debian packages in general, that might also be
part of the solution.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_stat_tmp

[Magnus Hagander]

On Mon, Dec 16, 2013 at 2:57 PM, Tim Kane tim.k...@gmail.com wrote:

 Hi all,

 The past few days I’ve been encountering the following error, followed by
 a full db restart and recovery


 2013-12-16 07:12:53 GMT LOG:  could not write temporary statistics file
 pg_stat_tmp/pgstat.tmp: No space left on device


 This occurs at a time of moderate load, during the same set of operations
 each morning.
 Interestingly, when I execute this manually at any other time of date, the
 process completes normally.

 I presume that the *pg_stat_tmp* location is system-wide and likely is
 not impacted by *temp_tablespaces*
 The root partition, where postgresql is installed does *not* have a lot
 of disk available (4GB).


 My first instinct here is to symlink *pg_stat_tmp* against another disk
 with a little more room to breathe, however I’m surprised that pgstat.tmp
 would grow to be so large in the first place – possibly there is something
 else at play here.

 I should note that this issue has only recently occurred, no major changes
 have been introduced.

 Thanks for any advice on this.


You can control the location of the file with the parameter
stats_temp_directory.

The size is most likely because the directory needs space for 1 copy of
the file, since it's written to a separate file and then renamed()d over
the existing one for atomicity. That said, even with that it shouldn't grow
*that* large, unless you've go a huge number of tables. Certainly not into
gigabyte range. So it could be that it's just the first indicator that
fires, but that there's actually something else using up your space. But
setting stats_temp_directory is a good first way to find that out.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] hstore syntax

[Magnus Hagander]

On Sun, Dec 8, 2013 at 11:22 AM, Kaare Rasmussen ka...@jasonic.dk wrote:

 Hi

 Trying to write a sql function to return hstore generated from a select.
 But I'm thinking there must be a better way.

 SELECT 'key = ' || s.part || '')::hstore

 is neither pretty nor secure. At least I need to escape any '' in s.part.
 I'll do so if there's no better way to write this (?)


If it's just for a single value, you can just use

SELECT hstore('key', s.part)

It also takes array (either one big array, or one array of keys and one
array of values).


Also, it seems there's no length or size function for hstore? This is what
 I've got working now array_length(akeys(s.hstore_field), 1). It seems to
 be a rather long way to do it, but it works.


That I don't think there is one, no.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] GSSAPI server side on Linux, SSPI client side on Windows

[Magnus Hagander]

On Tue, Nov 12, 2013 at 6:56 AM, Christian Ullrich ch...@chrullrich.netwrote:

 * From: Brian Crowell

  On Mon, Nov 11, 2013 at 10:51 PM, Brian Crowell br...@fluggo.com
 wrote:
   I think I'm getting closer though. I have psql on Windows successfully
   authenticating, so I can't be too far off.
 
  Got it.

 Great!

  The NpgsqlPasswordPacket class has a bug: a utility function it calls
  appends a null character to the data, which completely screws up GSSAPI.
  Now that I fixed that, I've got successful integrated authentication from
  Windows to PostgreSQL on Linux.
 
  However:
 
  * If I don't specify my username, Npgsql sends it in lowercase bcrowell

 Hmm. That is related one problem I've been having with SSPI auth from
 libpq/ODBC. The database treats the claimed user name case-sensitively when
 looking up the user info in pg_authid, and if the user logged on to Windows
 with a name differing in case from what the database thinks it is,
 authentication fails. Npgsql sending it always in lower case is precisely
 what I landed on as a workaround (basically overriding libpq's automatic
 user name detection in the ODBC connection string by appending a UID
 option).


Yes, windows is really annoying in this regard. It preserves what you
*logged in* with as your username - not what's stored in Active Directory
or the SAM.


 * Use kerberos package in AcquireCredentialsHandle call instead of
  negotiate

 As long as it is the client that does that, it should be fine. According
 to the documentation on SSPI packages, it is valid for the client SSPI to
 send a GSSAPI token to a server using the Negotiate package (instead of
 going through SPNEGO to arrive at the same protocol).


I believe it should be ok, yes.



  Also, in my case, it doesn't seem to matter for the SPN whether the
  service name is postgres or POSTGRES. I've got PostgreSQL set to

 Yeah, I think that bit about you have to make the service name uppercase
 in postgresql.conf is some kind of oral tradition that everyone quotes at
 everyone else. I have been using SSPI and GSSAPI since the days of Windows
 2000, and it has always worked quite well without it.


I think it also depends on which kerberos libraries you have on each end.
I've certainly had a lot of issues with it in the past, with Windows 2000
AD controllers and I think also Windows 2003.



  postgres, and Npgsql is specifying POSTGRES, but I also at some point
  configured two sets of SPNs on the domain for uppercase and lowercase, so
  I don't know if that's a mitigating factor.

 The client gets its service ticket, with only one service name in it,
 before contacting the server, so it cannot know what the server wants to
 see.

 Congratulations on getting it to work. I'm a bit envious that you beat me
 to it (GSS auth interop between PostgreSQL on Windows and others is kind of
 my hobby), but the sooner, the better.


+1 :)


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Using LDAP for PostgreSQL permissions/authentication

[Magnus Hagander]

On Fri, Sep 13, 2013 at 10:00 PM, Bill Moran wmo...@potentialtech.com wrote:

 Looking at using LDAP to ease the pain of maintaining user accounts across
 many tens of PostgreSQL servers ...

 As documented, LDAP solves a few of the problems we have -- since everyone
 will be in LDAP, we can use LDAP's password complexity rules and password
 expiration to handle those security requirements, and (of course) when
 someone changes their password, they don't have to remember to change it
 on every server ... these are big wins.

 But it doesn't help with the headache of creating the accounts on all the
 servers, or dropping them as part of employee termination procedures, or
 doing security audits, or changing permissions on multiple servers when
 an employee gets a promotion, etc.

 What would help with those challenges, is if PostgreSQL didn't need to have
 a mapping of LDAP - PostgreSQL account.  Instead, something where PostgreSQL
 checked some LDAP attribute(s) for a list of database roles and used those
 to determine what access to allow.

 For example:
 * In LDAP, have a user wmoran
  - That user has an attribute like databaseRoles=analyst,beancounter
 * In PostgreSQL, there is no user called wmoran
 * In PostgreSQL, there are roles called analyst and beancounter
 * Table permissions are GRANTed to analyst and/or beancounter as appropriate

 Thus, when I go to log in as wmoran, LDAP checks my password, then informs
 PostgreSQL to allow me in with specified roles, and I can do operations
 granted to those roles.

 Obviously, that's not how it works now ... my question is why not?  Is it just
 a matter of nobody's gotten to it yet, or are there issues that make such
 an implementation difficult/troublesome/impossible?  If it's possible, does
 anyone have any concept of how hard it would be to implement?

Pretty hard - but nothing is impossible. It would basically mean that
you have to update shared catalogs during login (e.g. the mapping
between user role and group roles). And you have to do it potentially
*before* logging in, because we'd need to be able to verify which
roles have CONNECT permissions on the database, or are members of the
proper role in AD.

It's not enough to just attach the information to the current session,
at least some of it needs to be persisted to deal with things like
object ownership etc, as Stephen pointed out.


It is, however, fairly easy to build a little script that synchronizes
the role membership from ldap to the database at regular intervals,
and at least for me, this has always been enough. If your LDAP server,
or user provisioning system for that one, has some sort of trigger
functionality, you can make it almost synchronous as well.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Strange message from pg_receivexlog

[Magnus Hagander]

On Tue, Aug 20, 2013 at 8:17 AM, Sergey Konoplev gray...@gmail.com wrote:
 Hi all,

 My WAL archiving script based on pg_receivexlog reported the following
 error several days ago (just ignore everything before
 'pg_receivexlog', it's a message my script generates).

 Thu Aug 15 18:33:09 MSK 2013 ERROR archive_wal.sh: Problem occured
 during WAL archiving: pg_receivexlog: could not send feedback packet:
 server closed the connection unexpectedly

 At the same time postgres reported this error in log:

 2013-08-15 18:32:51 MSK 30945 postgres@[unknown] from [local]
 [vxid:53/0 txid:0] [streaming 2A97/6FA48000] LOG:  terminating
 walsender process due to replication timeout

 Both pg_receivexlog and postgres run at the same machive,
 pg_receivexlog connects to postgres locally. /var/log/messages has
 absolutely nothing about it. I also have a hot standby on another
 machine connecting to the same master, but there is nothing strange in
 its logs either.

 Any thoughts what it was?

It looks like something outside postgres or pg_receivexlog did
terminate the connection. pg_receievexlog noticed it was closed,
whereas postgres waited for the timeout (but probably would've noticed
if it had actually had some other data to send maybe?). Do you have
some iptables connection tracking or something like that which could
be interfering?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Question re contribs of Julian Assange

[Magnus Hagander]

On Wed, Jul 17, 2013 at 9:15 AM, ERR ORR rd0...@gmail.com wrote:
 I noticed on the postgresql website that a certain Julian Assange is
 mentioned among the contributors to Postgresql.

 Out of curiosity, could anybody post in short what exactly he contributed to
 the DB?

You can find a list of his contributions at:
http://git.postgresql.org/gitweb/?p=postgresql.gita=searchh=HEADst=authors=julian

Not sure if there might be others as well that he didn't commit
himself, but back in the days I think he mostly did.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Force ssl connection

[Magnus Hagander]

On Wed, Jul 10, 2013 at 12:04 PM, Muhammad Bashir Al-Noimi
mbno...@gmail.com wrote:
 On Tue, Jul 9, 2013 at 11:21 PM, Jeff Janes jeff.ja...@gmail.com wrote:
 From your original email:
 db.setHostName(localhost);

 So localhost is probably matching 127.0.0.1/32 or ::1/128, which
 are explicitly allowed.

 I'm using this and still able to connect without SSL

If you want to make sure you can *never* connect without SSL, replace
all entries of host with hostssl. It makes no sense to require SSL
over localhost, but if that's what you want (or just for testing),
replace those too.


 db.setHostName(192.168.0.74);
 db.setPort(5433);
 // set requiressl=1 to enable SSL
 db.setConnectOptions(requiressl=0);

requiressl=0 doesn't mean what you think it means, and that's one
reason it has been deprecated since at least 8.2.

requiressl=0 means negotiate. use ssl if the server asks for it, but
accept not using ssl. So this will connect without an error both with
and without ssl.

If you want to enforce ssl, use sslmode=require.
If you want to enforce non-ssl, use sslmode=disable.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


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

[Magnus Hagander]

On Wed, Jun 26, 2013 at 3:47 AM, James Sewell james.sew...@lisasoft.comwrote:

 Hello All,

 Is there a way to disable chasing LDAP referrals in PostgreSQL?


There is not, at this point. It would probably be fairly trivial to add a
pg_hba parameter to turn it off (since it's, AFAIK, just a call to
ldap_set_option), but it's not possible to do with current versions of
PostgreSQL.

snip

You could try injecting a simple ldap_set_opttion() that turns off
LDAP_OPT_REFERRALS in InitializeLdapConnection() in auth.c, to see if that
works. If it solves the problem, it might be worth turning it into an
actual option.


If I run psql like so:

  psql -U d248265 -h 127.0.0.1 dccn

 then it hangs. From a packet capture I've determined that in this time it
 is trying to resolve the referral, which is broken and times out.


As you say, a quick fix would of course be to fix your server not to send
broken referrals ;)

But I can see how it could perhaps still be useful to be able to block
referrals. I think the reason it's not in there already is mainly that it
doesn't make much sense for the actual login bind - but it does make more
sense for the search step that happens before the bind.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] .pgpass being ignored

[Magnus Hagander]

On Sat, Jun 22, 2013 at 12:19 AM, Stephen Rasku sra...@gmail.com wrote:
 I am trying to write a script that will create and populate a
 database.  I don't want to enter a password every time so I want to
 use a .pgpass file.  It has the correct permissions:

 $ ls -l $PGPASSFILE
 -rw---  1 Stephen  staff  43 21 Jun 14:48 /Users/Stephen/.pgpass

 However, when I call createdb, it fails:

 $ createdb -h 192.168.1.4 -U postgres --no-password JobSearch
 createdb: could not connect to database postgres: fe_sendauth: no
 password supplied

 This is the contents of my .pgpass file:

 192.168.1.4:5432:DatabaseName:postgres:thisIsTheCorrectPassword

 If I omit the --no-password option it will prompt me for a password
 and the command will succeed.  I am using 9.0.10 from MacPorts.

 What am I doing wrong?

From the error message, the failure is to connect to the database
postgres. Your .pgpass only has an entry for database
DatabaseName.

createdb will connect to postgres and issue the CREATE DATABASE
command there. You can change tihs with the parameter
--maintenance-db, but tat was added in 9.2 only. But even so, createdb
must connect to an *existing* database in order to issue the CREATE
DATABASE command, so you need a line in .pgpass for whichever
maintenance db you're using.

(also, you should really upgrade to 9.0.13, though that won't change
this specific case)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] LDAP authentication timing out

[Magnus Hagander]

On Thu, Jun 20, 2013 at 7:24 AM, James Sewell james.sew...@lisasoft.comwrote:

 Hello All,

 I have the following config:

 hostsamerole+myrole samenetldap
 ldapserver=ldap1,ldap2,ldap3 ldapbinddn=mybinddn
 ldapbindpasswd=mypass ldapbasedn=mybase ldapsearchattribute=myatt

 Usually auth works perfectly with LDAP (starting a session from psql using
 an LDAP connection, authenticating with the LDAP password then exiting
 straight away) I see this:

 2013-06-20 15:19:53 EST DEBUG:  edb-postgres child[15901]: starting with (
 2013-06-20 15:19:53 EST DEBUG:  forked new backend, pid=15901 socket=10
 2013-06-20 15:19:53 EST DEBUG:  edb-postgres
 2013-06-20 15:19:53 EST DEBUG:  dccn
 2013-06-20 15:19:53 EST DEBUG:  )
 2013-06-20 15:19:53 EST DEBUG:  InitPostgres
 2013-06-20 15:19:53 EST DEBUG:  my backend ID is 1
 2013-06-20 15:19:53 EST DEBUG:  StartTransaction
 2013-06-20 15:19:53 EST DEBUG:  name: unnamed; blockState:   DEFAULT;
 state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 2013-06-20 15:19:53 EST DEBUG:  received password packet
 2013-06-20 15:19:53 EST DEBUG:  CommitTransaction
 2013-06-20 15:19:53 EST DEBUG:  name: unnamed; blockState:   STARTED;
 state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 2013-06-20 15:19:56 EST DEBUG:  shmem_exit(0): 7 callbacks to make
 2013-06-20 15:19:56 EST DEBUG:  proc_exit(0): 3 callbacks to make
 2013-06-20 15:19:56 EST DEBUG:  exit(0)
 2013-06-20 15:19:56 EST DEBUG:  shmem_exit(-1): 0 callbacks to make
 2013-06-20 15:19:56 EST DEBUG:  proc_exit(-1): 0 callbacks to make
 2013-06-20 15:19:56 EST DEBUG:  reaping dead processes
 2013-06-20 15:19:56 EST DEBUG:  server process (PID 15901) exited with
 exit code 0

 However around 10% of the time (although this varies) the session hangs
 after I type in my password till the auth timeout and I see this:

 2013-06-20 15:07:46 EST DEBUG:  forked new backend, pid=15587 socket=10
 2013-06-20 15:07:46 EST DEBUG:  edb-postgres child[15587]: starting with (
 2013-06-20 15:07:46 EST DEBUG:  edb-postgres
 2013-06-20 15:07:46 EST DEBUG:  dccn
 2013-06-20 15:07:46 EST DEBUG:  )
 2013-06-20 15:07:46 EST DEBUG:  InitPostgres
 2013-06-20 15:07:46 EST DEBUG:  my backend ID is 1
 2013-06-20 15:07:46 EST DEBUG:  StartTransaction
 2013-06-20 15:07:46 EST DEBUG:  name: unnamed; blockState:   DEFAULT;
 state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
 2013-06-20 15:07:46 EST DEBUG:  received password packet
 2013-06-20 15:08:46 EST DEBUG:  shmem_exit(1): 7 callbacks to make
 2013-06-20 15:08:46 EST DEBUG:  proc_exit(1): 3 callbacks to make
 2013-06-20 15:08:46 EST DEBUG:  exit(1)
 2013-06-20 15:08:46 EST DEBUG:  shmem_exit(-1): 0 callbacks to make
 2013-06-20 15:08:46 EST DEBUG:  proc_exit(-1): 0 callbacks to make
 2013-06-20 15:08:46 EST DEBUG:  reaping dead processes
 2013-06-20 15:08:46 EST DEBUG:  server process (PID 15587) exited with
 exit code 1

 Anyone have any ideas? I never see this with MD5.

 I can multiple quickfire binds from an LDAP application and the same bind
 DN with no problems.


Sounds like an issue either with your ldap server, your network or the ldap
client library. But it's kind of hard to tell. You're probably best off
getting a network trace of the traffic between the ldap server and
postgres, to see how far it gets at all  - that's usually a good pointer
when it comes to timeouts.

Also, what version of postgres (looks from the names that this might be edb
advanced server and not actually postgres? In that case you might be better
off talking to the EDB people - they may have made some modifications to
the ldap code perhaps)?

What OS?
Versions?
What ldap client and version?
What ldap server?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Snapshot backups

[Magnus Hagander]

On Thu, Jun 20, 2013 at 8:45 AM, James Sewell james.sew...@lisasoft.comwrote:

 Hey All,

 This is a message to confirm my thoughts / validate a possible approach.

 In a situation where PGDATA and {XLOG, ARCHIVELOG} are on different
 SAN/NAS volumes and a backup is to be initiated do pg_start_backup and
 pg_stop_backup need to be used?

 I am using snapshots of each volume for backup.

 My thinking is that they are not needed (although I realise it is good
 practice).

 As far as I can tell all they are doing is something like:

 pg_start_backup:
   - create backup label
   - trigger checkpoint

 pg_stop_backup
   - remove backup label file
   - creates backup history file
   - trigger log switch

 There is nothing in here that is *required* from a backup point of view.
 Am I missing anything?


The backup functions also set internal state in the database, so you can't
just replace it with doing those operations manually.  You do need to call
those functions.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] pg_basebackup, requested WAL has already been removed

[Magnus Hagander]

On Fri, May 10, 2013 at 7:00 PM, Sergey Koposov kopo...@ast.cam.ac.uk wrote:

 On Fri, 10 May 2013, Lonni J Friedman wrote:

 Its definitely not a bug.  You need to set/increase wal_keep_segments
 to a value that ensures that they aren't recycled faster than the time
 required to complete the base backup (plus some buffer).


 But I thought that wal_keep_segments is not needed for the streaming regime
 ( --xlog-method=stream)  And the documentation
 http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html
 only mentions wal_keep_segments when talking about --xlog-method=fetch.

It's not a bug in the software - this will happen if the background
stream in pg_basebackup cannot keep up, and that's normal. It works
the same way as a regular standby in that if it's unable to keep up it
will eventually fall so far behind that it can't recover.

It may definitely be something that needs to be cleared up in the
documentation though.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] What's wrong with postgresql.org domain?

[Magnus Hagander]

On Thu, Apr 18, 2013 at 10:31 AM, Eduardo Morras emorr...@yahoo.es wrote:


 I get Godaddy's page saying it's free

Really?

Whois shows it expires Oct 21 - and surely it will be renewed by then.
and godaddy says it's registered (though no details).

Any chance you just spelled it wrong?


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] What's wrong with postgresql.org domain?

[Magnus Hagander]

On Thu, Apr 18, 2013 at 10:37 AM, Eduardo Morras emorr...@yahoo.es wrote:
 On Thu, 18 Apr 2013 10:33:20 +0200
 Magnus Hagander mag...@hagander.net wrote:

 On Thu, Apr 18, 2013 at 10:31 AM, Eduardo Morras emorr...@yahoo.es wrote:
 
 
  I get Godaddy's page saying it's free

 Really?

 Whois shows it expires Oct 21 - and surely it will be renewed by then.
 and godaddy says it's registered (though no details).

 Any chance you just spelled it wrong?


 Retrying access it using a search engine:

 https://duckduckgo.com/?q=postgresql

 PostgreSQL
   [Official Site]
 postgresql.org   More from postgresql.org

 Welcome to: postgresql.org
 This Web page is parked for FREE, courtesy of GoDaddy.com.

 ...

 Don't know what happens.

Interesting. I thought duckduckgo was supposed to deliver the same
results to everybody, but I'm not getting that when I click your link.
Nothing about parked or godaddy.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Git host for postgresql related projects

[Magnus Hagander]

On Sat, Apr 13, 2013 at 9:10 PM, Francisco Figueiredo Jr.
franci...@npgsql.org wrote:

 Hi all!


 I was thinking about moving Npgsql from cvs to another SCM. Possibly git.

 I'd like to know if there is any postgresql project host which supports git
 which you could recommend.

 I know there is git.postgresql.org and github.

 From the new repository page of git.postgresql.org it says: [...] If you
 want to to use git, while also getting some of the features that are on
 pgfoundry, we strongly advise you to look at github. [...]

 It would be better for me, then, to use github? As this clearly is my case.

 Thanks in advance for your help and advice.

I would suggest going with github. They provide you with a bunch of
services like a wiki and an issue tracker that you will not get from
git.postgesql.org. They also provide you with the social coding
stuff or whatever they call it (their forking/pull request/whatnot
stuff) if you like it - but you can also just use it as a pure git
repo if you prefer.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] High CPU usage of stats collector

[Magnus Hagander]

On Sun, Mar 31, 2013 at 11:44 PM, komunca komu...@gmail.com wrote:
 On my db server I have noticed a very high CPU usage of process:
 *postgres: stats collector process*

 It is not below 50% of CPU. (Info from htop).

 What is best way to decrease CPU usage of this process?

Do you by any chance have a large number of databases, and/or a large
number of objects in your databases (tables or indexes)?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] FW: statement failure

[Magnus Hagander]

On Thu, Mar 7, 2013 at 5:33 PM, Little, Douglas
douglas.lit...@orbitz.com wrote:
 both dev and prod are 8.2.15

 Version stringPostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1) on
 x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct  3
 2012 20:28:56



 can you tell me what change in 8.3  maybe Greenplum incorporated some of the
 changes in their build.

Since they're not actually 8.2, you should probably ask Greenplum
about the differences - I'm sure their support people will know.

What Merlin was referring to is the removal of implicit casts - it's
listed as the very first point under migration to 8.3 back in the old
release notes (which can still be found as a chapter in the 9.2
release notes for example, even though 8.3 is fully unsupported by
now)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] could not link file pg_xlog/xlogtemp.72606

[Magnus Hagander]

On Fri, Feb 15, 2013 at 6:25 PM, Claire McLister mclis...@zeesource.net wrote:
 Hi there,

  We recently copied over our 8.3 database cluster files from a local disk to
 a network attached storage drive.

  Everything seemed to work okay for a while until we got this error:

  ERROR:  could not link file pg_xlog/000100AA0030 to
 pg_xlog/000100AA0096 (initialization of log file 170, segment
 150): Operation not supported

  pg_xlog/000100AA0096 does not exist in the original file tree,
 so I'm wondering if there's some special link that PG is trying to create
 that is not permitted in the NAS drive?

That's a regular (hard) link. Whether it's supported or not would
depend on your NAS, but it certainly looks like it's not supported by
yours - or you need different mount options. For example, if your NAS
runs something Windows-based, it's likely not supporting links at all.

Note that putting the data directory on a NAS is in general not a good
idea - it's very easy to get corruption that way, unless you are very
careful.



  This is for Postgresql 8.3 + PostGIS 1.5

Also, do note that PostgreSQL 8.3 is no longer supported - see
http://www.postgresql.org/support/versioning/. You should look into
upgrading ASAP.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Guidance on building Foreign Data Wrapper on Windows.

[Magnus Hagander]

On Tue, Feb 12, 2013 at 8:05 AM, Silk Parrot silkpar...@gmail.com wrote:
 Hi Hackers,

 I am interested in writing a FDW. I setup my environment on Windows.
 However, I am not able to figure out how to build FDW successfully.  Here is
 what I have done:

 -Install the 9.2.2 by using EnterpriseDB installer. Add bin/ to the PATH
 -I have followed the windows build instruction here:
 http://www.postgresql.org/docs/9.2/static/install-windows-full.html. I can
 run and debug it in Visual Studio.

 But when I tried to build any existing FDW via cygwin, it just tells me
 this:
 C:/PostgreSQL/9.2/lib/pgxs/src/makefiels/pgxs.mk: No such file or directory

 There is no pgxs.mk in that folder.

You cannot use cygwin to build with the native win32 version. You
should probably use mingw instead. Though even with that you probably
don't get the pgxs files - you will also need to do a source install
of PostgreSQL itself somewhere to get the required build files. This
is because the official binaries are built with the Microsoft
compilers, which don't have a build system compatible with pgxs.

You can also build it with the free compilers from MS, but thta will
at this point require you to manually set up a project file in Visual
Studio Express - but for something as simple as an FDW, that shouldn't
be every hard.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] PG V9 on NFS

[Magnus Hagander]

On Tue, Feb 12, 2013 at 2:25 AM, Devrim GÜNDÜZ dev...@gunduz.org wrote:

 Hi,

 On Mon, 2013-02-11 at 22:22 +, Gauthier, Dave wrote:
 Can PG V9.1* support a DB that's on an NFS disk?

 http://www.postgresql.org/message-id/25517.1191038...@sss.pgh.pa.us

Note that that email is more than 5 years old. A lot has happened in
the linux kernel since then.

It's perfectly possible to run PostgreSQL reliable on NFS. But it's
quite hard to set all the parameters correct to get both decent
performance and some sort of reliability. And it's very easy to get it
wrong, and end up with data corruption. If you don't know exactly what
to do there, I would definitely advice you not to try it.

And don't even consider it without a high-end dedicated filer as the backend.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Changing Character Sets

[Magnus Hagander]

On Tue, Feb 12, 2013 at 5:34 PM, Tim Gustafson t...@ucsc.edu wrote:
 Is there any way to change the character set of a database and its tables?

 I did a pg_dumpall to upgrade from Postgres 8.4 to Postgres 9.2, and
 all the tables came back as UTF-8, and now Bacula is complaining that
 it wants SQL_ASCII encoding for everything.  I don't see a flag on
 pg_dumpall or pg_restore to set which character encoding I'd like, and
 Google has failed me.

What you're looking for is to change the encoding, right, and not the locale?

You can't change the encoding of a database, but you can use a
different one when you create it - this can be specified in the CREATE
DATABASE statement.

You can also ask pg_dump to use a specific encoding using the -E
parameter. You can't do it on pg_dumpall, but you can do it if you use
pg_dump.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Changing Character Sets

[Magnus Hagander]

On Tue, Feb 12, 2013 at 6:02 PM, Tim Gustafson t...@ucsc.edu wrote:
 What you're looking for is to change the encoding, right, and not the locale?

 Correct.

 You can't change the encoding of a database, but you can use a
 different one when you create it - this can be specified in the CREATE
 DATABASE statement.

 That's what I wound up doing.

 You can also ask pg_dump to use a specific encoding using the -E
 parameter. You can't do it on pg_dumpall, but you can do it if you use
 pg_dump.

 That's good to know.

 I'm curious why a pg_dumpall from 8.4 followed by a restore in 9.2
 caused the character sets to change at all.  Was there some change in
 the default character sets between 8.4 and 9.2?

That depends on your platform, but the answer is likely yes. Or you
had manually specified SQL_ASCII in the 8.4 method. SQL_ASCII
basically means don't care at all about encodings.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Hot Standby has PANIC: WAL contains references to invalid pages

[Magnus Hagander]

On Thu, Feb 7, 2013 at 7:39 AM, amutu zhao6...@gmail.com wrote:
 maybe pg_basebackup can`t handle such big database.try
 rsync,pg_start_backup,rsync,pg_stop_backup,it always works fine for us.our
 instance is about 2TB and we use pg9.1.x.

It really should handle that without problem, but sure, it might be
worth trying that one. If you can show that the problem is in
pg_basebackup, that's a very clear bug (either in pg_basebackup or in
the backend supporting code), so that would be good to know.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Version numbers for binaries

[Magnus Hagander]

On Thu, Jan 31, 2013 at 12:20 AM, deepak deepak...@gmail.com wrote:
 Hi !

 We bundle Postgres into a Windows MSI, Postgres built with VS2008.

 One of the issues we ran into recently is Windows getting confused with the
 file
 versions of Postgres binaries, confused meaning, it was treating newer
 binaries
 as older, and hence skipping copying certain files during an upgrade.

 Looking further, I came across the file where version numbers for some of
 the
 binaries are generated (including pg_ctl.exe), and it used to use 2 digit
 year
 followed by day of year, without any padding.  We need to pad the day of
 year
 with leading zeros to make the version non-decreasing.

 I've included a patch below, could this be patched into Postgres?

Thanks, I've applied a change like this (though with the description
mainly in the commit message and not in a code comment, so not using
your patch) for head and backpatched it into the supported branches,
as it's clearly wrong.

For future reference, please post patches to the pgsql-hackers
mailinglist, instead of the pgsql-general, to make sure that it gets
the attention of the developers. It worked this time, but that's
better for next time.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Throttling Streamming Replication

[Magnus Hagander]

On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da Silva
rodr...@paripassu.com.br wrote:
 Hi Guys,

 We are having a problem with our infrastructure provider because the network
 traffic between master and slave server is reaching more than 30k packages
 per second(SLA says 20k/second).
 Is there any way to throttle the streamming replication? I meant, any
 parameter that I set the max number of megabytes sent to standby server per
 second?
 I didn't have any luck looking at the postgresql streamming replication
 documentation. There is the wal_sender_delay, but I suppose that if I set
 more than 1 second, it could accumulate a bunch of wal files and send it at
 once. So, it wouldn't work.

No, there is no such parameter. You might be able to send it through
some proxy that slows it down, but there is no builtin support to do
that.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


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

[Magnus Hagander]

On Jan 21, 2013 7:17 AM, bhanu udaya udayabhanu1...@hotmail.com wrote:


  Hello,
 Greetings !
 Thank you for the prompt reply. I have changed the settings as listed
below:
   shared_buffers = 1024MB
   work_mem = 512MB
   maintenance_work_mem = 512MB
   wal_buffers = 100MB
  fsync = off # ONLY DURING INITIAL DATA LOAD!
  checkpoint_segments = 128 # large value, such as 128 or 256 (16MB per
file, check disk space)
  checkpoint_timeout = 30min
  checkpoint_completion_target = 0.9
  wal_level = minimal # You'll need to do a full base backup if you use
this
 But, have same problem. It is almost 1 hour now, the restoration is still
going on.  After every test case execution, we would like to refresh the
database and expected refresh should be completed in less than 10 minutes.
Is this achievable with the kind of configuration I have listed in my
earlier email.

 Kindly help , as how to speed up this restoration process.


Try running pg_restore with the -1 option. If that doesn't help, try -m4 or
something like that (you'll have to remove the first option then, can't use
both at once)

But it's going to be pushing it anyway. Your scenario is going to create
thousands of files (assuming you have multiple tables in each of your
schemas as is normal), and that's just not something ntfs does very fast.
Once the files are there, I bet loading the data is reasonably fast since
it can't be all that big

/Magnus

Re: [GENERAL] pg_basebackup questions

[Magnus Hagander]

On Fri, Nov 30, 2012 at 7:28 PM, hartrc rha...@mt.gov wrote:
 Hi,

 PostgreSQL 9.1.6
 Linux (SLES 11)

 i have a pg_basebackup job that is executed within a script.
 My command is:

 /postgresql/v91/bin/pg_basebackup -h 127.0.0.1 -D
 /postgresql/pg_backup/backupdir -Ft -Z 5

 The result of this creates 2 files: base.tar and 16450.tar

 I understand that 16450 is the oid of the additional tablespace i created
 however this tablespace resides within the data directory so as far as i can
 tell (and have tested) suggests this is a duplicate.

Why do you have a separate tablespace within the data directory?!


 I did a test restore and was able to recover all tablespaces from the
 base.tar without any issues - confirming my suspicion that 16450.tar is
 essentially an uneeded duplicate backup.

 What pg_basebackup command would i run to avoid created of 16450.tar ?

There is no way to do that. What you should do is move your tablespace
outside the main data directory. If you want the data in the main data
directory, there is no point to creating the tablespace.


 Another thing is that during pg_basebackup i get warning:
 pg_basebackupWARNING:  could not read symbolic link
 pg_tblspc/mytablespacename: Invalid argument

 Again based on my successful recovery i'm assuming this is not a major issue
 but it is still a little disconcerting.

This indicates that you have something in pg_tblspc that is not a
symlink. That's never supposed to happen, so your data directory is
corrupt. It's probably not something that actually causes a problem
other than this one in the backup - for now at least. But the fix is
moving the tablespace to a proper location.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
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] Non-replicated index allowing comparison ()?

[Magnus Hagander]

On Nov 29, 2012 8:59 PM, Björn Edström bj...@spotify.com wrote:

 Greetings list.

 I have a Postgresql 9.0.8 cluster with one master and a few slaves, in
 a hot standby and streaming replication setup. The setup has a single
 database with a single table. The table has a column updated of type
 timestamp.

 For maintenance reasons I'd like to create a B-tree (or at least some
 kind of index allowing fast comparisons,  and  at least).
 However, I'd, if possible, like to create this index on a single
 machine in the cluster and not have it replicated everywhere. When the
 maintenance is done the index should be removed. Hash indices will not
 work in this case - it's non-replicated but does not work for
 comparisons.

 Ideas?

This is not possible with streaming replication. You'd have to look at one
of the external replication solutions out there, like Slony. In sr, you
need to create it on the master and have it replicate everywhere.

/Magnus