Re: [GENERAL] md5(large_object_id)

[Karsten Hilbert]

On Wed, Oct 07, 2015 at 01:01:41PM -0500, Jim Nasby wrote:

> >Still, I'd welcome a native, streaming md5(loid) which is
> >bound to be more optimized by design.
> 
> It would be nice if we had an interface to TOAST that allowed for streaming
> (well, really chunking) data to a function. That wouldn't help in this
> particular case, but it would significantly expand the usefulness of a
> streaming version of md5 and all the other hash operators.

Sure enough. A native md5(oid_of_large_object), which
internally uses lo_read(), didn't seem all that much work to
me.

Maybe I'm wrong on that side of things.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] postgres standby won't start

[Ramalingam, Sankarakumar]

Ok, I did the pg_basebackup successfully and sent the file (.tar) to the target 
and expanded it over the data directory. Looks like I am still out of sync when 
trying to open the DB(or start it).

2015-10-07 16:04:13 EDT FATAL:  timeline 26 of the primary does not match 
recovery target timeline 22

Any ideas?



Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, October 07, 2015 1:25 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 10:02 AM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> Thanks for your prompt response. I used pg_basebackup to backup my source 
> server. I am starting this exercise on our QA side so I can do a dry run 
> before hitting production. Would I be able to restore that backup on my 
> target standby? If yes, would you guide me to the steps. Thanks a lot for 
> your invaluable suggestions overall to this forum.

The steps can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

Look at the examples at the bottom and substitute the data directory for the 
standby in the -D portion. Important:

-D directory
--pgdata=directory

 Directory to write the output to. pg_basebackup will create the directory 
and any parent directories if necessary. The directory may already exist, but 
it is an error if the directory already exists and is not empty.

To be on safe side you could move the current files in the standby directory to 
another location before pointing pg_basebackup at it.

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Monday, October 05, 2015 7:35 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
>> Thanks Adrian.
>>
>> My primary is doing  fine. Only the standby. I am noticing it after a 
>> while..my Bad!!
>> Primary setting
>> #
>> -
>> -
>> # REPLICATION
>> #
>> -
>> -
>>
>> max_wal_senders = 5
>> wal_sender_delay = 1s
>> wal_keep_segments = 512
>> vacuum_defer_cleanup_age = 20
>> hot_standby = on
>>
>> Since I am unable to bring standby up, would the pg_basebackup help me out 
>> here for a complete sync? Thanks again.
>
> The problem is that the WAL files got recycled on the primary and are no 
> longer available there, which is why the standby is failing. If you where 
> archiving the WAL files somewhere, you could fetch the missing WAL files from 
> there. If that is not the case then you will need to rebuild the standby to 
> the current state(more or less) of the primary. The simplest way is to use 
> pg_backup, but that means starting with either no data directory or an empty 
> one on the standby. The docs page steps you through the procedure.
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> The information contained in this e-mail and in any attachments is intended 
> only for the person or entity to which it is addressed and may contain 
> confidential and/or privileged material. Any review, retransmission, 
> dissemination or other use of, or taking of any action in reliance upon, this 
> information by persons or entities other than the intended recipient is 
> prohibited. This message has been scanned for known computer viruses.
>


--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.



-- 
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] Best practices for aggregate table design

[droberts]

I see the advantage is for the developer.  We right one REST API call that
leverages this single table regardless whether he wants groups by city for a
month or total for a month.  Creating a separate table would make the
backend a bit more complex is all and wouldn't save on space I don't think. 



--
View this message in context: 
http://postgresql.nabble.com/Best-practices-for-aggregate-table-design-tp5868940p5869195.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] postgres standby won't start

[Ramalingam, Sankarakumar]

Hello Adrian,
Can I use this command so that all the data is streamed directly to the target. 
Target is pretty much unusable now. Should I get rid of all items under my 
$PGDATA and leave the empty before doing the following? 

pg_basebackup -h   -D   -P -U 
replication --xlog-method=stream



Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, October 07, 2015 1:25 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 10:02 AM, Ramalingam, Sankarakumar wrote:
> Hi Adrian,
> Thanks for your prompt response. I used pg_basebackup to backup my source 
> server. I am starting this exercise on our QA side so I can do a dry run 
> before hitting production. Would I be able to restore that backup on my 
> target standby? If yes, would you guide me to the steps. Thanks a lot for 
> your invaluable suggestions overall to this forum.

The steps can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

Look at the examples at the bottom and substitute the data directory for the 
standby in the -D portion. Important:

-D directory
--pgdata=directory

 Directory to write the output to. pg_basebackup will create the directory 
and any parent directories if necessary. The directory may already exist, but 
it is an error if the directory already exists and is not empty.

To be on safe side you could move the current files in the standby directory to 
another location before pointing pg_basebackup at it.

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Monday, October 05, 2015 7:35 PM
> To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] postgres standby won't start
>
> On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
>> Thanks Adrian.
>>
>> My primary is doing  fine. Only the standby. I am noticing it after a 
>> while..my Bad!!
>> Primary setting
>> #
>> -
>> -
>> # REPLICATION
>> #
>> -
>> -
>>
>> max_wal_senders = 5
>> wal_sender_delay = 1s
>> wal_keep_segments = 512
>> vacuum_defer_cleanup_age = 20
>> hot_standby = on
>>
>> Since I am unable to bring standby up, would the pg_basebackup help me out 
>> here for a complete sync? Thanks again.
>
> The problem is that the WAL files got recycled on the primary and are no 
> longer available there, which is why the standby is failing. If you where 
> archiving the WAL files somewhere, you could fetch the missing WAL files from 
> there. If that is not the case then you will need to rebuild the standby to 
> the current state(more or less) of the primary. The simplest way is to use 
> pg_backup, but that means starting with either no data directory or an empty 
> one on the standby. The docs page steps you through the procedure.
>
>>
>>
>>
>> Thanks
>> Kumar Ramalingam
>> X6015288
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> The information contained in this e-mail and in any attachments is intended 
> only for the person or entity to which it is addressed and may contain 
> confidential and/or privileged material. Any review, retransmission, 
> dissemination or other use of, or taking of any action in reliance upon, this 
> information by persons or entities other than the intended recipient is 
> prohibited. This message has been scanned for known computer viruses.
>


--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.



-- 
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] postgres standby won't start

[Adrian Klaver]

On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:

Hello Adrian,
Can I use this command so that all the data is streamed directly to the target. 
Target is pretty much unusable now. Should I get rid of all items under my 
$PGDATA and leave the empty before doing the following?

pg_basebackup -h   -D   -P -U 
replication --xlog-method=stream


Just to be sure the target is the standby server, correct?

If so you do not want this:

-h 

-h is for the server you are taking the backup of, or to put it another 
way the flow is from -h to -D.


If you are running pg_basebackup on the same machine as the primary then:

pg_basebackup  -D   -P -U replication 
--xlog-method=stream


This assumes there is only one instance of Postgres running on that 
machine and it is running on port 5432. If not then you will need to 
specify -p 


pg_basebackup is a client program, so like other such programs you have 
to tell it what database(cluster) to connect to using the normal 
switches, see below. Actually pg_basebackup does not connect to a 
specific database but to the database cluster, so any database name you 
supply will be ignored.


http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"The following command-line options control the database connection 
parameters."






Thanks
Kumar Ramalingam
X6015288






--
Adrian Klaver
adrian.kla...@aklaver.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] Issues with german locale on CentOS 5,6,7

[Peter Geoghegan]

On Wed, Oct 7, 2015 at 8:06 PM, Thomas Munro
 wrote:
>> I think we should bite the bullet and adopt ICU, without abandoning
>> support for OS locales for users that really need it (certainly, many
>> will need it initially when using pg_upgrade to get on to the first
>> version that happens to have ICU support). I don't like suggesting a
>> solution that I myself am unlikely to find the time to work on, but in
>> the long run that's the only sensible approach IMV.
>
> How would you handle changes in ICU's collation definitions?

ICU provides an API for collation versioning because of these kinds of
issues with indexes:
http://userguide.icu-project.org/collation/architecture#TOC-Versioning

There are specifications of collations used by ICU that originate from
the Unicode CLDR Project: http://cldr.unicode.org/

Basically, you prevent this kind of thing from ever happening in the
first place by making versioning explicit, and putting it under the
direct control of Postgres. I think a bunch of well regarded database
systems have used ICU for many years, including DB2, for example. I
think it's possible to arrange it so that the collations simply never
go away, but if that does happen (or if you decide that the changes to
a collation matter for cultural or correctness reasons) then you can
at least detect the change and recover from it reliably.

ICU has some other really nice features, too, but that's another discussion.

-- 
Regards,
Peter Geoghegan


-- 
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] postgres standby won't start

[Ramalingam, Sankarakumar]

Yes target is a standby on another machine. We have two sites (geographically 
separated)



Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, October 07, 2015 8:06 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
> Hello Adrian,
> Can I use this command so that all the data is streamed directly to the 
> target. Target is pretty much unusable now. Should I get rid of all items 
> under my $PGDATA and leave the empty before doing the following?
>
> pg_basebackup -h   -D   
> -P -U replication --xlog-method=stream

Just to be sure the target is the standby server, correct?

If so you do not want this:

-h 

-h is for the server you are taking the backup of, or to put it another way the 
flow is from -h to -D.

If you are running pg_basebackup on the same machine as the primary then:

pg_basebackup  -D   -P -U replication 
--xlog-method=stream

This assumes there is only one instance of Postgres running on that machine and 
it is running on port 5432. If not then you will need to specify -p 


pg_basebackup is a client program, so like other such programs you have to tell 
it what database(cluster) to connect to using the normal switches, see below. 
Actually pg_basebackup does not connect to a specific database but to the 
database cluster, so any database name you supply will be ignored.

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"The following command-line options control the database connection parameters."

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.



-- 
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] Issues with german locale on CentOS 5,6,7

[Thomas Munro]

On Thu, Oct 8, 2015 at 5:52 AM, Peter Geoghegan
 wrote:
> On Wed, Oct 7, 2015 at 6:25 AM, Tom Lane  wrote:
>> The only real way out of such a situation is to REINDEX affected indexes.
>> Refusing to start the server not only doesn't contribute to a solution,
>> but makes it impossible to fix manually.
>
> I agree that that would be almost as bad as carrying on, because there
> is no reason to think that the locale thing can easily be rolled back.
> That was my point, in fact.

I have contemplated a maintenance script that would track either the
md5 checksums of the /usr/lib/locale/*/LC_COLLATE files or the version
of installed locale packages and automatically reindex things when
they change (I guess after restarting the cluster to clear any glibc
caches that might be lurking in long running backends).  Or at least
tell me that's needed.  Obviously completely OS-specific...

-- 
Thomas Munro
http://www.enterprisedb.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] Issues with german locale on CentOS 5,6,7

[Tom Lane]

Peter Geoghegan  writes:
> I think we should bite the bullet and adopt ICU,

I see absolutely nothing to recommend that course of action.  Reasons not
to:

1. Being compatible with the operating system's collation behavior is a
feature, not a bug.  If nothing else, it allows us to tell people that
if we sort data the same way that sort(1) does, then it's not a bug that
we're not sorting the way they think we should.  But quite aside from
that, there are practical uses to being compatible with other tools.

2. Last I checked, ICU *only* supports Unicode, and not only that, but
only UTF16.  This is a non-starter; not only for our Far Eastern users,
but also those who find various LatinX encodings sufficient.  ICU would be
a functional fail for the former and a performance fail for the latter.

3. As Thomas Munro already noted, whatcha gonna do when ICU changes their
collations?  Or are their collations graven on stone tablets, unlike
anyone else's?


We certainly could stand to put some work into the problem of coping with
collation changes.  But claiming that ICU is the solution, or even a
solution, seems obviously wrong.

regards, tom lane


-- 
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] Issues with german locale on CentOS 5,6,7

[Peter Geoghegan]

On Wed, Oct 7, 2015 at 8:39 PM, Tom Lane  wrote:
> 1. Being compatible with the operating system's collation behavior is a
> feature, not a bug.  If nothing else, it allows us to tell people that
> if we sort data the same way that sort(1) does, then it's not a bug that
> we're not sorting the way they think we should.  But quite aside from
> that, there are practical uses to being compatible with other tools.

I am not proposing to make that impossible.

> 2. Last I checked, ICU *only* supports Unicode, and not only that, but
> only UTF16.  This is a non-starter; not only for our Far Eastern users,
> but also those who find various LatinX encodings sufficient.  ICU would be
> a functional fail for the former and a performance fail for the latter.

UTF-16 is more efficient for representing East Asian languages, so not
sure what you mean about that. I realize that using UTF-16 is a
non-starter, though.

I guess you were talking about people who don't use Unicode due to the
Han Unification controversy. Again, I'm not proposing to only support
Unicode, but realistically the vast majority of users want Unicode,
even in East Asia.

Yes, ICU only supports Unicode, but it has supported UTF-8 for years
now, and not as a second class citizen. See
http://userguide.icu-project.org/strings/utf-8 . As it says there:

"""
If it is known that the default charset is always UTF-8 on the target
platform, then you should #define U_CHARSET_IS_UTF8 1 in or before
unicode/utypes.h. (For example, modify the default value there or pass
-DU_CHARSET_IS_UTF8=1 as a compiler flag.) This will change most of
the implementation code to use dedicated (simpler, faster) UTF-8 code
paths and avoid dependencies on the conversion framework. (Avoiding
such dependencies helps with statically linked libraries and may allow
the use of UCONFIG_NO_LEGACY_CONVERSION or even UCONFIG_NO_CONVERSION
[see unicode/uconfig.h].)

"""

> 3. As Thomas Munro already noted, whatcha gonna do when ICU changes their
> collations?  Or are their collations graven on stone tablets, unlike
> anyone else's?

See my response to Thomas.

-- 
Regards,
Peter Geoghegan


-- 
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] postgres standby won't start

[Ramalingam, Sankarakumar]

Hi Adrian,
After a few attempts I used the following (without method=stream as it is not 
working on 9.1) and restored the DB on standby site successfully. The log says 
it is connecting to primary
streaming replication successfully connected to primary

but still complains about some log files. 

2015-10-08 00:47:37 EDT LOG:  streaming replication successfully connected to 
primary
2015-10-08 00:53:53 EDT LOG:  received smart shutdown request
2015-10-08 00:53:53 EDT FATAL:  terminating walreceiver process due to 
administrator command
2015-10-08 00:53:53 EDT LOG:  shutting down
2015-10-08 00:53:53 EDT LOG:  database system is shut down
2015-10-08 00:54:04 EDT LOG:  database system was shut down in recovery at 
2015-10-08 00:53:53 EDT
cp: cannot stat `/db_logs/pgsql/archive/001A.history': No such file or 
directory
2015-10-08 00:54:04 EDT LOG:  entering standby mode
cp: cannot stat `/db_logs/pgsql/archive/001A084500C9': No such file 
or directory
2015-10-08 00:54:04 EDT LOG:  redo starts at 845/C920
2015-10-08 00:54:04 EDT LOG:  consistent recovery state reached at 845/CA00
2015-10-08 00:54:04 EDT LOG:  database system is ready to accept read only 
connections
cp: cannot stat `/db_logs/pgsql/archive/001A084500CA': No such file 
or directory
2015-10-08 00:54:04 EDT LOG:  streaming replication successfully connected to 
primary
2015-10-08 00:56:21 EDT LOG:  connection received: host=[local]
2015-10-08 00:56:21 EDT LOG:  connection authorized: user=postgres 
database=clients


Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, October 07, 2015 8:06 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/07/2015 01:30 PM, Ramalingam, Sankarakumar wrote:
> Hello Adrian,
> Can I use this command so that all the data is streamed directly to the 
> target. Target is pretty much unusable now. Should I get rid of all items 
> under my $PGDATA and leave the empty before doing the following?
>
> pg_basebackup -h   -D   
> -P -U replication --xlog-method=stream

Just to be sure the target is the standby server, correct?

If so you do not want this:

-h 

-h is for the server you are taking the backup of, or to put it another way the 
flow is from -h to -D.

If you are running pg_basebackup on the same machine as the primary then:

pg_basebackup  -D   -P -U replication 
--xlog-method=stream

This assumes there is only one instance of Postgres running on that machine and 
it is running on port 5432. If not then you will need to specify -p 


pg_basebackup is a client program, so like other such programs you have to tell 
it what database(cluster) to connect to using the normal switches, see below. 
Actually pg_basebackup does not connect to a specific database but to the 
database cluster, so any database name you supply will be ignored.

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

"The following command-line options control the database connection parameters."

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.



-- 
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] Issues with german locale on CentOS 5,6,7

[Peter Geoghegan]

On Wed, Oct 7, 2015 at 4:49 PM, Thomas Munro
 wrote:
>> I agree that that would be almost as bad as carrying on, because there
>> is no reason to think that the locale thing can easily be rolled back.
>> That was my point, in fact.
>
> I have contemplated a maintenance script that would track either the
> md5 checksums of the /usr/lib/locale/*/LC_COLLATE files or the version
> of installed locale packages and automatically reindex things when
> they change (I guess after restarting the cluster to clear any glibc
> caches that might be lurking in long running backends).  Or at least
> tell me that's needed.  Obviously completely OS-specific...

I think we should bite the bullet and adopt ICU, without abandoning
support for OS locales for users that really need it (certainly, many
will need it initially when using pg_upgrade to get on to the first
version that happens to have ICU support). I don't like suggesting a
solution that I myself am unlikely to find the time to work on, but in
the long run that's the only sensible approach IMV.

-- 
Regards,
Peter Geoghegan


-- 
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] Issues with german locale on CentOS 5,6,7

[Thomas Munro]

On Thu, Oct 8, 2015 at 1:16 PM, Peter Geoghegan
 wrote:
> On Wed, Oct 7, 2015 at 4:49 PM, Thomas Munro
>  wrote:
>>> I agree that that would be almost as bad as carrying on, because there
>>> is no reason to think that the locale thing can easily be rolled back.
>>> That was my point, in fact.
>>
>> I have contemplated a maintenance script that would track either the
>> md5 checksums of the /usr/lib/locale/*/LC_COLLATE files or the version
>> of installed locale packages and automatically reindex things when
>> they change (I guess after restarting the cluster to clear any glibc
>> caches that might be lurking in long running backends).  Or at least
>> tell me that's needed.  Obviously completely OS-specific...
>
> I think we should bite the bullet and adopt ICU, without abandoning
> support for OS locales for users that really need it (certainly, many
> will need it initially when using pg_upgrade to get on to the first
> version that happens to have ICU support). I don't like suggesting a
> solution that I myself am unlikely to find the time to work on, but in
> the long run that's the only sensible approach IMV.

How would you handle changes in ICU's collation definitions?

-- 
Thomas Munro
http://www.enterprisedb.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] Best practices for aggregate table design

[Thomas Kellerer]

droberts schrieb am 06.10.2015 um 20:53:
> Okay, so is it safe to say I should use loosely use these guidelines when
> deciding whether to model an attribute as a dimension
> (type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ?
> 
> If you know the number of values for a dimension are fixed (e.g. boolean),
> then creating a measure will have benefits of:
>   - reduced number of rows/storage
>   - better performance since less indexing/vacuuming  
> 
> the drawbacks are:
>   -rigid structure, not very extensible over time (e.g. later realize I need
> to also track 'internal' calls). 
> 
> In my case, I'm now needing to add another measure 'encrypted=true/false',
> so my table is starting to look like 

Have you considered using a hstore column to store the attributes you don't 
know yet?

Which makes this extensible, flexible and fast. 





-- 
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] How to drop user if objects depend on it

[Andrus]

The example script works for me.  What PG version are you running?  I have
a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
the past.


I'm using

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit"

In   "PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit" it works.

It looks like in 9.1  reassign owned should replaced with revoke commands.

Andrus. 




--
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] backup.old

[David G. Johnston]

On Wed, Oct 7, 2015 at 3:16 PM, Steve Pribyl 
wrote:

> Great, dur(rtfm), so is it save to delete the backup.old, if the db is not
> in backup mode.
>
​I don't see anything that would cause "backup.old" to be linked to an
active backup if there was one in progress...and as far as I can tell a
successfully completed backup doesn't leave around any such file either.
So it should be safe to delete regardless of what pg_is_in_backup returns.
If that returns true then I would be concerned that something (or someone)
else is messing with the backup/data directory ​and that something likely
also introduced backup.old

David J.

[GENERAL] PostGIS 2.2.0 Released

[Paul Ramsey]

http://postgis.net/2015/10/07/postgis-2.2.0

PostGIS 2.2.0 is released! Over the last two years a number of interesting new 
features have been added, such as:

* True nearest-neighbor searching for all geometry and geography types
* New volumetric geometry support, including ST_3DDifference, ST_3DUnion and 
more
* Temporal data model support and functions like ST_ClosestPointOfApproach to 
support temporal query
* Spatial clustering functions ST_ClusterIntersecting and ST_ClusterWithin
* Subdividing large geometries with ST_Subdivide
* Fast box clipping with ST_ClipByBox2D
* In-database raster processing with ST_Retile and ST_CreateOverview
* New high-speed native code address standardizer
* Visvalingam-Whyatt geometry simplification with ST_SimplifyVW
* Support for compressed “tiny well-known binary” format with ST_AsTWKB and 
ST_GeomFromTWKB

See the full list of changes in the news file: 
  http://svn.osgeo.org/postgis/tags/2.2.0/NEWS

Thanks to everyone who helped with testing and development over this cycle!

Yours,
Team PostGIS
 


-- 
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] Best practices for aggregate table design

[Marc Mamin]

>2. I'm adding a 'null' row to show all the calls for a given month
>regardless of city or state, again to simplify the client side. It adds a
>row and is somewhat sparse but preferrable by the developer. Acceptable
>practice?

do you see any advantage with this model? 
I would store your monthly data within a separate table.

regards,
Marc Mamin

-- 
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] backup.old

[Steve Pribyl]

Thank you very much.  I read someplace if you run pg_start_backup twice the 
backup.old will be created, but there was not much beyond that and now I can't 
seem to find the reference.


Steve Pribyl
Sr. Systems Engineer
steve.pri...@akunacapital.com
Desk: 312-994-4646




From: David G. Johnston 
Sent: Wednesday, October 7, 2015 2:25 PM
To: Steve Pribyl
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] backup.old

On Wed, Oct 7, 2015 at 3:16 PM, Steve Pribyl 
> wrote:

Great, dur(rtfm), so is it save to delete the backup.old, if the db is not in 
backup mode.

I don't see anything that would cause "backup.old" to be linked to an active 
backup if there was one in progress...and as far as I can tell a successfully 
completed backup doesn't leave around any such file either.  So it should be 
safe to delete regardless of what pg_is_in_backup returns.  If that returns 
true then I would be concerned that something (or someone) else is messing with 
the backup/data directory and that something likely also introduced backup.old

David J.


[http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 

p: +1 312 994 4646 | m: | f: +1 312 750 1667 | steve.pri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.

Re: [GENERAL] backup.old

[David G. Johnston]

On Wed, Oct 7, 2015 at 3:29 PM, Steve Pribyl 
wrote:

> Thank you very much.  I read someplace if you run pg_start_backup twice
> the backup.old will be created, but there was not much beyond that and now
> I can't seem to find the reference.
>

​Scanning the docs and logic tells me that attempting to do pg_start_backup
twice in a row should result in the second attempt giving an error...but I
could be misinformed.

The file pg_start_backup creates is named "backup_label" and so I'd also
expect any attempt to add an old suffix would keep the same base name...

David J.​

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

"Andrus"  writes:
>> The example script works for me.  What PG version are you running?  I have
>> a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
>> the past.

> I'm using
> "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
> (Debian 4.4.5-8) 4.4.5, 64-bit"

Ah.  I believe this is fixed in 9.1.7 and later.  In prior versions DROP
OWNED neglects to revoke permissions on shared objects (databases,
tablespaces).

Considering that 9.1.x is up to 9.1.19 as of tomorrow, you really ought
to think about doing a minor-version upgrade.  We don't put out bug fix
releases just for idle amusement.

regards, tom lane


-- 
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] backup.old

[Scott Mead]

On Wed, Oct 7, 2015 at 15:38, David G. Johnston 


wrote:
On Wed, Oct 7, 2015 at 3:29 PM, Steve Pribyl < 
steve.pri...@akunacapital.com [steve.pri...@akunacapital.com] > wrote:

Thank you very much. I read someplace if you run pg_start_backup twice the
backup.old will be created, but there was not much beyond that and now I 
can't

seem to find the reference.


backup_label gets deleted on pg_stop_backup() on the *master*.
Backup_label will still be in the *backup* itself however (or, more 
succinctly,
a slave server). When you start the backup / slave, it will process 
backup_label

so that it can start recovery. Once we don't need it anymore, the file is
renamed to backup_label.old. Typically, when you see a backup_label.old on 
a
writable master, it was either: * a backup that was restored and put in to 
service * a slave server that was promoted A pg_controldata will probably 
show a timeline != 1




Scanning the docs and logic tells me that attempting to do pg_start_backup 
twice
in a row should result in the second attempt giving an error...but I could 
be

misinformed.
The file pg_start_backup creates is named "backup_label" and so I'd also 
expect

any attempt to add an old suffix would keep the same base name...
David J.

[GENERAL] Issues with german locale on CentOS 5,6,7

[Bernd Helmle]

The last day we've encountered an issue what i think is somewhat severe if
you want to do either OS upgrades with CentOS or even binary upgrades with
an existing PostgreSQL instance to a new machine with locale de_DE.UTF-8
and thus i'd like to share here. 

Here are the details:

Originally a Postgres 9.4 was running on CentOS 5.11/x86_64. The database
in question was initialized with locale de_DE.UTF-8 and previously upgraded
via pg_upgrade from 9.2 and then running without any issues for a while.

After that the customer migrated to new hardware with an OS upgrade to
CentOS 6.6/x86_64. This was done by just remounting the SAN LUN on the new
machine. So far so good, no issues. 

However, after a while developers realized duplicate values in unique keys
with certain types of string values (the format is described in the
examples below). So the suspicion was that this has to do with locales. And
yes, the german locale collation order changed:

CentOS 5.11 has:

echo -e '156\n1-5-6\n110\n1-1-0' | LANG=de_DE.UTF-8 sort
110
1-1-0
156
1-5-6

CentOS 6.6 does:

echo -e '159\n1-5-9\n110\n1-1-0' | LANG=de_DE.UTF-8 sort
1-1-0
110
1-5-9
159

Interestingly CentOS 7.1 restores the behavior from CentOS 5.11

echo -e '159\n1-5-9\n110\n1-1-0' | LANG=de_DE.UTF-8 sort
110
1-1-0
159
1-5-9

There are entries in the CentOS bugtracker regarding other locales:

https://bugs.centos.org/view.php?id=7009
https://bugs.centos.org/view.php?id=6210

So users are encouraged to carefully test their platforms when upgrading.
Checks show that at least RHEL6 and RHEL7 have the same issue, too.

-- 
Thanks

Bernd



-- 
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] Issues with german locale on CentOS 5,6,7

[Bernd Helmle]

--On 7. Oktober 2015 02:33:59 -0700 Peter Geoghegan
 wrote:

> 
> At the risk of getting flamed: I think that this is a bug in
> PostgreSQL, not CentOS. I've said why I think that is at least once
> already [1]. Simply put, there is no justification for the belief that
> some people have that collations should be immutable, and there is
> some justification for OS vendors updating them in a way that
> disregards our use of the collations. Glibc actually versions
> collations, although that isn't in any standard, and it isn't clear
> that we can benefit from that beyond refusing to start the server when
> a collation was superseded by a new version.
> 

We had exactly the same discussion here. I tried it to express carefully
since i just wanted to hint any german users, but given that many database
vendors out there have their own collation definitions shipped shows
clearly that this is something we cannot ignore anymore.

> [1]
> http://www.postgresql.org/message-id/CAEYLb_UTMgM2V_pP7qnuKZYmTYXoym-zNYV
> bwoU79=tup8h...@mail.gmail.com

Yeah, i know this discussion and SR clearly widens the window for such
things to happen.

-- 
Thanks

Bernd


-- 
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] md5(large_object_id)

[Karsten Hilbert]

On Wed, Oct 07, 2015 at 12:55:38PM +0200, Karsten Hilbert wrote:

> > > I am dealing with radiology studies aka DICOM data) one would
> > > want an md5 function which streams in parts of a large object
> > > piece by piece using md5_update and m5_finalize or some such.
> > It would certainly be possible to write a lo_md5(oid) function to do
> > this, but as far as I'm aware nobody has yet done so.  How are your
> > C skills?
> 
> I had hoped someone was going to say: "Yeah, right, low
> hanging fruit, let's just do it for 9.next" :-)

Someone _with_ C skills, that is.

Thanks for the plperlu suggestion, btw.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Issues with german locale on CentOS 5,6,7

[Peter Geoghegan]

On Wed, Oct 7, 2015 at 2:20 AM, Bernd Helmle  wrote:
> The last day we've encountered an issue what i think is somewhat severe if
> you want to do either OS upgrades with CentOS or even binary upgrades with
> an existing PostgreSQL instance to a new machine with locale de_DE.UTF-8
> and thus i'd like to share here.

At the risk of getting flamed: I think that this is a bug in
PostgreSQL, not CentOS. I've said why I think that is at least once
already [1]. Simply put, there is no justification for the belief that
some people have that collations should be immutable, and there is
some justification for OS vendors updating them in a way that
disregards our use of the collations. Glibc actually versions
collations, although that isn't in any standard, and it isn't clear
that we can benefit from that beyond refusing to start the server when
a collation was superseded by a new version.

[1] 
http://www.postgresql.org/message-id/CAEYLb_UTMgM2V_pP7qnuKZYmTYXoym-zNYVbwoU79=tup8h...@mail.gmail.com
-- 
Regards,
Peter Geoghegan


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

Ynt: [GENERAL] How to drop user if objects depend on it

[Neslisah Demirci]

Hi ,


REASSIGN OWNED -- change the ownership of database objects owned by a database 
role.


REASSIGN OWNED BY old_role [, ...] TO new_role

You can create a new role then you just assign database objects depend on old 
role.
REASSIGN owned by old_role to new_role;

Then

DROP old_role;

Is this helpful?

Neslisah.


Gönderen: pgsql-general-ow...@postgresql.org 
 adına Andrus 
Gönderildi: 07 Ekim 2015 Çarşamba 13:42
Kime: pgsql-general
Konu: [GENERAL] How to drop user if objects depend on it

Hi!

Database idd owner is role idd_owner
Database has 2 data schemas: public and firma1.
User may have directly or indirectly assigned rights in this database and 
objects.
User is not owner of any object. It has only rights assigned to objects.

How to drop such  user ?

I tried

revoke all on all tables in schema public,firma1 from "vantaa" cascade;
revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
revoke all on database idd from "vantaa" cascade;
revoke all on all functions in schema public,firma1 from "vantaa" cascade;
revoke all on schema public,firma1 from "vantaa" cascade;
revoke idd_owner from "vantaa" cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from 
"vantaa";
DROP ROLE if exists "vantaa"

but got error

role "vantaa" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public

in statement

DROP ROLE if exists "vantaa"

How to fix this so that user can dropped ?

How to create sql or plpgsql method which takes user name as parameter and 
drops this user in all cases without dropping data ?
Or maybe there is some command or simpler commands in postgres ?


Using Postgres 9.1+
Posted also in

http://stackoverflow.com/questions/32988702/how-to-drop-user-in-all-cases-in-postgres
[http://cdn.sstatic.net/stackoverflow/img/apple-touch-i...@2.png?v=73d79a89bded]

sql - How to drop user in postgres if it has depending objects - Stack Overflow
Database idd owner is role idd_owner Database has 2 data schemas: public and 
firma1. User may have directly or indirectly assigned rights in this database 
and objects. User is not owner of any ob...
Devamını 
okuyun...




Andrus.

Re: [GENERAL] md5(large_object_id)

[Pavel Stehule]

2015-10-07 13:18 GMT+02:00 Karsten Hilbert :

> On Wed, Oct 07, 2015 at 12:55:38PM +0200, Karsten Hilbert wrote:
>
> > > > I am dealing with radiology studies aka DICOM data) one would
> > > > want an md5 function which streams in parts of a large object
> > > > piece by piece using md5_update and m5_finalize or some such.
> > > It would certainly be possible to write a lo_md5(oid) function to do
> > > this, but as far as I'm aware nobody has yet done so.  How are your
> > > C skills?
> >
> > I had hoped someone was going to say: "Yeah, right, low
> > hanging fruit, let's just do it for 9.next" :-)
>
> Someone _with_ C skills, that is.
>

if the size of blobs is less than 1GB, then it should be possible in
plpgsql too.

 postgres=# \lo_import ~/Desktop/001.jpg
lo_import 24577
postgres=# select md5(lo_get(24577));
┌──┐
│   md5│
╞══╡
│ 610ccaab8c7c60e1168abfa799d1305d │
└──┘
(1 row)

Regards

Pavel


> Thanks for the plperlu suggestion, btw.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> 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] md5(large_object_id)

[Karsten Hilbert]

On Wed, Oct 07, 2015 at 01:30:24PM +0200, Pavel Stehule wrote:

> > > > > I am dealing with radiology studies aka DICOM data) one would
> > > > > want an md5 function which streams in parts of a large object
> > > > > piece by piece using md5_update and m5_finalize or some such.
> > > > It would certainly be possible to write a lo_md5(oid) function to do
> > > > this, but as far as I'm aware nobody has yet done so.  How are your
> > > > C skills?
> > >
> > > I had hoped someone was going to say: "Yeah, right, low
> > > hanging fruit, let's just do it for 9.next" :-)
> >
> > Someone _with_ C skills, that is.
> >
> 
> if the size of blobs is less than 1GB, then it should be possible in
> plpgsql too.
> 
>  postgres=# \lo_import ~/Desktop/001.jpg
> lo_import 24577
> postgres=# select md5(lo_get(24577));
> ┌──┐
> │   md5│
> ╞══╡
> │ 610ccaab8c7c60e1168abfa799d1305d │
> └──┘
> (1 row)

It is, I know. but I am exactly after the use case > 1 GB

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


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

[GENERAL] How to drop user if objects depend on it

[Andrus]

Hi!

Database idd owner is role idd_owner 
Database has 2 data schemas: public and firma1. 
User may have directly or indirectly assigned rights in this database and 
objects.
User is not owner of any object. It has only rights assigned to objects.

How to drop such  user ?

I tried

revoke all on all tables in schema public,firma1 from "vantaa" cascade;
revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
revoke all on database idd from "vantaa" cascade;
revoke all on all functions in schema public,firma1 from "vantaa" cascade;
revoke all on schema public,firma1 from "vantaa" cascade;
revoke idd_owner from "vantaa" cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from 
"vantaa";
DROP ROLE if exists "vantaa"

but got error

role "vantaa" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public

in statement 

DROP ROLE if exists "vantaa"

How to fix this so that user can dropped ?

How to create sql or plpgsql method which takes user name as parameter and 
drops this user in all cases without dropping data ?
Or maybe there is some command or simpler commands in postgres ?


Using Postgres 9.1+
Posted also in 

http://stackoverflow.com/questions/32988702/how-to-drop-user-in-all-cases-in-postgres


Andrus.

Re: [GENERAL] Issues with german locale on CentOS 5,6,7

[Thomas Kellerer]

Peter Geoghegan schrieb am 07.10.2015 um 11:33:
> On Wed, Oct 7, 2015 at 2:20 AM, Bernd Helmle  wrote:
>> The last day we've encountered an issue what i think is somewhat severe if
>> you want to do either OS upgrades with CentOS or even binary upgrades with
>> an existing PostgreSQL instance to a new machine with locale de_DE.UTF-8
>> and thus i'd like to share here.
> 
> At the risk of getting flamed: I think that this is a bug in
> PostgreSQL, not CentOS. I've said why I think that is at least once
> already [1]. Simply put, there is no justification for the belief that
> some people have that collations should be immutable, and there is
> some justification for OS vendors updating them in a way that
> disregards our use of the collations. Glibc actually versions
> collations, although that isn't in any standard, and it isn't clear
> that we can benefit from that beyond refusing to start the server when
> a collation was superseded by a new version.

I for one am not going to flame you ;) 

I always thought that this is a major shortcoming (if not a bug) in Postgres 
that the collation support is left to the OS. 

Because it essentially means that that exactly the same query with exactly the 
same data might return a different result if run on different OS

Thomas





-- 
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] md5(large_object_id)

[Karsten Hilbert]

On Mon, Oct 05, 2015 at 03:27:26PM +, Kevin Grittner wrote:

> Karsten Hilbert  wrote:
> 
> > I am dealing with radiology studies aka DICOM data) one would
> > want an md5 function which streams in parts of a large object
> > piece by piece using md5_update and m5_finalize or some such.
> It would certainly be possible to write a lo_md5(oid) function to do
> this, but as far as I'm aware nobody has yet done so.  How are your
> C skills?

I had hoped someone was going to say: "Yeah, right, low
hanging fruit, let's just do it for 9.next" :-)

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] How to drop user if objects depend on it

[Thom Brown]

On 7 October 2015 at 11:42, Andrus  wrote:
> Hi!
>
> Database idd owner is role idd_owner
> Database has 2 data schemas: public and firma1.
> User may have directly or indirectly assigned rights in this database and
> objects.
> User is not owner of any object. It has only rights assigned to objects.
>
> How to drop such  user ?
>
> I tried
>
> revoke all on all tables in schema public,firma1 from "vantaa" cascade;
> revoke all on all sequences in schema public,firma1 from "vantaa"
> cascade;
> revoke all on database idd from "vantaa" cascade;
> revoke all on all functions in schema public,firma1 from "vantaa"
> cascade;
> revoke all on schema public,firma1 from "vantaa" cascade;
> revoke idd_owner from "vantaa" cascade;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES
> from "vantaa";
> DROP ROLE if exists "vantaa"
>
> but got error
>
> role "vantaa" cannot be dropped because some objects depend on it
> DETAIL:  privileges for schema public
>
> in statement
>
> DROP ROLE if exists "vantaa"
>
> How to fix this so that user can dropped ?
>
> How to create sql or plpgsql method which takes user name as parameter and
> drops this user in all cases without dropping data ?
> Or maybe there is some command or simpler commands in postgres ?

The objects can't be owned by nothing, so you will need to reassign ownership:

REASSIGN OWNED BY old_role TO new_role;

e.g.

REASSIGN OWNED BY vantaa TO postgres;

Then you can drop the role.

Regards

Thom


-- 
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] Issues with german locale on CentOS 5,6,7

[Tom Lane]

Peter Geoghegan  writes:
> At the risk of getting flamed: I think that this is a bug in
> PostgreSQL, not CentOS. I've said why I think that is at least once
> already [1]. Simply put, there is no justification for the belief that
> some people have that collations should be immutable, and there is
> some justification for OS vendors updating them in a way that
> disregards our use of the collations. Glibc actually versions
> collations, although that isn't in any standard, and it isn't clear
> that we can benefit from that beyond refusing to start the server when
> a collation was superseded by a new version.

I can think of less helpful responses to such a situation, but not easily.

The only real way out of such a situation is to REINDEX affected indexes.
Refusing to start the server not only doesn't contribute to a solution,
but makes it impossible to fix manually.

regards, tom lane


-- 
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] How to drop user if objects depend on it

[Melvin Davidson]

Can you connect as user postgres? IE: psql -U postgres -d 

If so, then you should have the ability to execute the commands without any
problem.

On Wed, Oct 7, 2015 at 9:53 AM, Adrian Klaver 
wrote:

> On 10/07/2015 05:12 AM, Andrus wrote:
>
>> Hi!
>>
>> The objects can't be owned by nothing, so you will need to reassign
>>> ownership:
>>> REASSIGN OWNED BY old_role TO new_role;
>>> e.g.
>>> REASSIGN OWNED BY vantaa TO postgres;
>>> Then you can drop the role.
>>>
>>
>> User who deletes other users is not superuser. It is created using
>>
>> CREATE ROLE admin LOGIN
>>   NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
>> 100;
>> GRANT idd_owner TO admin;
>>
>>
>> I tried
>>
>
> So to be clear admin is doing the below, correct?
>
>
>> REASSIGN OWNED BY vantaa TO postgres;
>>
>> and
>>
>> REASSIGN OWNED BY vantaa TO idd_owner;
>>
>> but got  error
>>
>> permission denied to reassign objects .
>>
>
> Is the above a blanket error or does it mention specific objects?
>
>
>> How to fix ?
>>
>
> What you are trying to do is reverse what you did to get the present
> setup. Do you have a record/script that shows what you did to create the
> role and assign it to the objects?
>
>
>> I can add some rights to user who invokes this command if this helps.
>>
>> Andrus.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to drop user if objects depend on it

[Andrus]

Hi!

The objects can't be owned by nothing, so you will need to reassign 
ownership:

REASSIGN OWNED BY old_role TO new_role;
e.g.
REASSIGN OWNED BY vantaa TO postgres;
Then you can drop the role.


User who deletes other users is not superuser. It is created using

CREATE ROLE admin LOGIN
 NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT 
100;

GRANT idd_owner TO admin;


I tried

REASSIGN OWNED BY vantaa TO postgres;

and

REASSIGN OWNED BY vantaa TO idd_owner;

but got  error

permission denied to reassign objects .

How to fix ?

I can add some rights to user who invokes this command if this helps.

Andrus. 




--
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] How to drop user if objects depend on it

[Adrian Klaver]

On 10/07/2015 05:12 AM, Andrus wrote:

Hi!


The objects can't be owned by nothing, so you will need to reassign
ownership:
REASSIGN OWNED BY old_role TO new_role;
e.g.
REASSIGN OWNED BY vantaa TO postgres;
Then you can drop the role.


User who deletes other users is not superuser. It is created using

CREATE ROLE admin LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
100;
GRANT idd_owner TO admin;


I tried


So to be clear admin is doing the below, correct?



REASSIGN OWNED BY vantaa TO postgres;

and

REASSIGN OWNED BY vantaa TO idd_owner;

but got  error

permission denied to reassign objects .


Is the above a blanket error or does it mention specific objects?



How to fix ?


What you are trying to do is reverse what you did to get the present 
setup. Do you have a record/script that shows what you did to create the 
role and assign it to the objects?




I can add some rights to user who invokes this command if this helps.

Andrus.





--
Adrian Klaver
adrian.kla...@aklaver.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] How to drop user if objects depend on it

[Andrus]

Hi!

No. You need to be a superuser to reassign objects unless you own the 
object.

1. first connect as user postgres
2. REASSIGN all the tables owned by the missing user first.
3. Then you can drop the missing user AFTER you have reassigned all the 
objects they own.


Script

reassign owned by farukkugay to postgres;
drop user farukkugay ;

is running by superuser but it still causes the error.



You must also be a superuser to drop roles.


Non-superuser creates roles, assigns rights and drop users using scripts 
which I provided.

Those scripts work OK on most cases.

For some users, vantaa and farukkugan  delete script causes error which I 
described. For farukkugan it occurs also if running under superuser.


So it looks like it should be possible for non-superusers also.

Andrus. 




--
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] How to drop user if objects depend on it

[Andrus]

Hi!


I tried
So to be clear admin is doing the below, correct?


Yes. I copied provided user definition which invokes delete command from 
pgadmin

code window for this user .


permission denied to reassign objects .
Is the above a blanket error or does it mention specific objects?


postgres log file contains two lines:

ERROR:  permission denied to reassign objects
STATEMENT:  reassign owned by vantaa to postgres

No objects are mentioned.
I can change log level if this helps.

What you are trying to do is reverse what you did to get the present setup. 
Do you have a record/script that shows what you did to create the role and 
assign it to the objects?


It was something like:

create script:

CREATE ROLE vantaa;
grant idd_owner to vantaa;

reset roles script:

revoke all on all tables in schema public,firma1 from vantaa cascade;
revoke all on all sequences in schema public,firma1 from vantaa cascade;
revoke all on database idd  from public,firma1 cascade;
revoke all on all functions in schema public,firma1 from vantaa cascade;
revoke all on schema public,firma1 from vantaa cascade;
revoke idd_owner  from vantaa cascade;
ALTER ROLE vantaa inherit NOCREATEROLE NOCREATEDB NOLOGIN;

grant all on all tables in schema public,firma1 to vantaa;
grant all on all sequences in schema public,firma1 to vantaa;
grant all on database idd  to vantaa;
grant all on schema public,firma1 to vantaa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO 
vantaa;

-- Restrict some tables:
revoke all on  kasutaja,kaspriv,logifail from vantaa cascade;
grant select on kaspriv,kasutaja to vantaa;
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup) 
on kasutaja to vantaa;

grant insert on logifail to vantaa;

Andrus. 




--
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] How to drop user if objects depend on it

[Melvin Davidson]

No. You need to be a superuser to reassign objects unless you own the
object.
You must also be a superuser to drop roles.

So.
1. first connect as user postgres
2. REASSIGN all the tables owned by the missing user first.
3. Then you can drop the missing user AFTER you have reassigned all the
objects they own.

On Wed, Oct 7, 2015 at 10:48 AM, Andrus  wrote:

> Hi!
>
> Can you connect as user postgres? IE: psql -U postgres -d 
>>
>
> Applicaton has admin users which should be able to delete other users.
> Those users dont have superuser rights.
>
> I can connect as user postgres for testing only.
> I'm looking for a way to delete users without superuser right.
>
> If so, then you should have the ability to execute the commands without
>> any problem.
>>
>
> I tried in database ktp :
>
> reassign owned by farukkugay to postgres;
> drop user farukkugay ;
>
> This causes error
>
> ERROR:  role "farukkugay" cannot be dropped because some objects depend on
> it
> DETAIL:  privileges for schema public
>
> So even superuser cannot delete.
>
> Andrus.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

"Andrus"  writes:
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO 
> vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.

In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.

regards, tom lane


-- 
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] md5(large_object_id)

[Karsten Hilbert]

For the record - I have also devised another solution to the
underlying problem (md5(bytea) cannot process large amounts
of input), chunked md5():

create or replace function lo_chunked_md5(oid, int)
returns text
language 'plpgsql'
stable strict
as '
DECLARE
lo_id alias for $1;
chunk_size alias for $2;
_lo_fd integer;
_lo_size integer;
_chunk_count integer;
_remainder integer;
_md5_concat text;
INV_READ  constant integer := x''4''::integer;
SEEK_SET  constant integer := 0;
SEEK_END  constant integer := 2;
BEGIN
-- check for existence of lo_id

_lo_fd := lo_open(lo_id, INV_READ);
-- get size
_lo_size := lo_lseek(_lo_fd, 0, SEEK_END);
PERFORM lo_close(_lo_fd);
-- calculate chunks and remainder
_chunk_count := _lo_size / chunk_size;
_remainder := _lo_size % chunk_size;
-- loop over chunks
_md5_concat := ;
FOR _chunk_id in 1.._chunk_count LOOP
_md5_concat := _md5_concat || md5(lo_get(lo_id, 
(_chunk_id - 1) * chunk_size, chunk_size));
END LOOP;
-- add remainder
_md5_concat := _md5_concat || md5(lo_get(lo_id, _chunk_count * 
chunk_size, _remainder));
return md5(_md5_concat);
END;';

This can easily be mirrored by a client-side function, say,

def file2chunked_md5(filename=None, chunk_size=500*_MB):
_log.debug('chunked_md5(%s, %s)', filename, chunk_size)
md5_concat = u''
f = open(filename, 'rb')
while True:
md5 = hashlib.md5()
data = f.read(chunk_size)
if not data:
break
md5.update(data)
md5_concat += md5.hexdigest()
f.close()

md5 = hashlib.md5()
md5.update(md5_concat)
hex_digest = md5.hexdigest()

_log.debug('md5(%s): %s', md5_concat, hex_digest)

return hexdigest

in Python.

Still, I'd welcome a native, streaming md5(loid) which is
bound to be more optimized by design.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] How to drop user if objects depend on it

[Andrus]

Hi!


Can you connect as user postgres? IE: psql -U postgres -d 


Applicaton has admin users which should be able to delete other users.
Those users dont have superuser rights.

I can connect as user postgres for testing only.
I'm looking for a way to delete users without superuser right.

If so, then you should have the ability to execute the commands without any 
problem.


I tried in database ktp :

reassign owned by farukkugay to postgres;
drop user farukkugay ;

This causes error

ERROR:  role "farukkugay" cannot be dropped because some objects depend on 
it

DETAIL:  privileges for schema public

So even superuser cannot delete.

Andrus. 




--
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] postgres standby won't start

[Adrian Klaver]

On 10/07/2015 10:02 AM, Ramalingam, Sankarakumar wrote:

Hi Adrian,
Thanks for your prompt response. I used pg_basebackup to backup my source 
server. I am starting this exercise on our QA side so I can do a dry run before 
hitting production. Would I be able to restore that backup on my target 
standby? If yes, would you guide me to the steps. Thanks a lot for your 
invaluable suggestions overall to this forum.


The steps can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

Look at the examples at the bottom and substitute the data directory for 
the standby in the -D portion. Important:


-D directory
--pgdata=directory

Directory to write the output to. pg_basebackup will create the 
directory and any parent directories if necessary. The directory may 
already exist, but it is an error if the directory already exists and is 
not empty.


To be on safe side you could move the current files in the standby 
directory to another location before pointing pg_basebackup at it.






Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Monday, October 05, 2015 7:35 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:

Thanks Adrian.

My primary is doing  fine. Only the standby. I am noticing it after a while..my 
Bad!!
Primary setting
#-
-
# REPLICATION
#-
-

max_wal_senders = 5
wal_sender_delay = 1s
wal_keep_segments = 512
vacuum_defer_cleanup_age = 20
hot_standby = on

Since I am unable to bring standby up, would the pg_basebackup help me out here 
for a complete sync? Thanks again.


The problem is that the WAL files got recycled on the primary and are no longer 
available there, which is why the standby is failing. If you where archiving 
the WAL files somewhere, you could fetch the missing WAL files from there. If 
that is not the case then you will need to rebuild the standby to the current 
state(more or less) of the primary. The simplest way is to use pg_backup, but 
that means starting with either no data directory or an empty one on the 
standby. The docs page steps you through the procedure.





Thanks
Kumar Ramalingam
X6015288






--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.




--
Adrian Klaver
adrian.kla...@aklaver.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] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

[Jim Nasby]

On 10/4/15 4:16 AM, Emre Hasegeli wrote:

The other line of attack would be to deprecate all the fuzzy comparison
behavior in the geometric types, and just make it exact.  Don't know how
much pain that would add, but surely it would also subtract some.


How can we go forward about this?  The current state of the operators
stand in the way of improving index support for them.


Your easiest way forward right now is probably to create an exact 
equality operator for point and the relevant hash opclass. That should 
be enough to let IS DISTINCT do it's thing.


BTW, it occurred to me that we could create btree opclasses as well, if 
we explicitly compared X before Y (or vice-versa). I certainly wouldn't 
call those operators < or >, but maybe there's enough use to supporting 
btree for this to make sense.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] How to drop user if objects depend on it

[Melvin Davidson]

Andrus,

>is running by superuser but it still causes the error.

That does not sound right. Please verify you are running as user postgres
with:

SELECT current_user;

Then make sure postgres is still a superuser with:

SELECT rolname as user,
   CASE WHEN rolcanlogin THEN 'user'
ELSE 'group'
   END,
   CASE WHEN rolsuper THEN 'SUPERUSER'
ELSE 'normal'
END AS super
  FROM pg_authid
WHERE rolname = 'postgres';

If you still get errors, then please show the exact error to us.


On Wed, Oct 7, 2015 at 11:11 AM, Andrus  wrote:

> Hi!
>
> No. You need to be a superuser to reassign objects unless you own the
>> object.
>> 1. first connect as user postgres
>> 2. REASSIGN all the tables owned by the missing user first.
>> 3. Then you can drop the missing user AFTER you have reassigned all the
>> objects they own.
>>
>
> Script
>
> reassign owned by farukkugay to postgres;
> drop user farukkugay ;
>
> is running by superuser but it still causes the error.
>
>
> You must also be a superuser to drop roles.
>>
>
> Non-superuser creates roles, assigns rights and drop users using scripts
> which I provided.
> Those scripts work OK on most cases.
>
> For some users, vantaa and farukkugan  delete script causes error which I
> described. For farukkugan it occurs also if running under superuser.
>
> So it looks like it should be possible for non-superusers also.
>
> Andrus.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] using postgresql for session

[john.tiger]

has anyone used postgres jsonb for holding session ?  Since server side 
session is really just a piece of data, why bother with special 
"session" plugins and just use postgres to hold the data and retrieve it 
with psycopg2 ?  Maybe use some trigger if session changes?We are 
using python Bottle with psycopg2 (super simple, powerful combo) - are 
we missing something magical about session plugins ?



--
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] How to drop user if objects depend on it

[Tom Lane]

"Andrus"  writes:
>> A little further review shows that DROP OWNED is the way to get rid of
>> leftover privileges.  So in general you need to do REASSIGN OWNED to move
>> the ownership of objects, then DROP OWNED to get rid of privileges granted
>> on non-owned objects, before you can drop a role.

> I tried this in database mydb using script below but still got error
> ERROR:  role "vantaa" cannot be dropped because some objects depend on it
> DETAIL:  privileges for database mydb

The example script works for me.  What PG version are you running?  I have
a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
the past.

regards, tom lane


-- 
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] backup.old

[Steve Pribyl]

Good Afternoon,

We are in a bit of pickle with this as I think some of the issues we may be 
having could possible be caused by being backup mode for months.  

What can I check or do to make sure the db is not in backup mode and under what 
circumstances/how can I remove that backup.old file?
 
Thanks
Steve Pribyl



From: pgsql-general-ow...@postgresql.org  
on behalf of Steve Pribyl 
Sent: Tuesday, October 6, 2015 10:47 PM
To: John R Pierce; pgsql-general@postgresql.org
Subject: Re: [GENERAL] backup.old

Honestly I don't know.  It was either pg_dump or a recovery script, that would 
have included a call to pg_start_backup.   It does have the start_backup 
looking format.

START WAL LOCATION: F3/128 (file 004500F30001)
CHECKPOINT LOCATION: F3/160
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2015-05-30 01:39:24 CDT
LABEL: base-backup

Steve Pribyl
Sr. Systems Engineer
steve.pri...@akunacapital.com
Desk: 312-994-4646



From: pgsql-general-ow...@postgresql.org  
on behalf of John R Pierce 
Sent: Tuesday, October 6, 2015 10:43 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] backup.old

On 10/6/2015 8:28 PM, Steve Pribyl wrote:
> What do I need to do to recover a database server that has a backup.old file 
> in the data_directory.  I have see references to a database being stopped 
> during a backup and/or a second backup running and moving the old file aside, 
> but it was not clear to me what needs to be done do recover.
>
> postgresql-9.3   9.3.0-2.pgdg12.4+1


what method or software were you using to perform this backup?


--
john r pierce, recycling bits in santa cruz



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

 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 

p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | steve.pri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.


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


-- 
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] backup.old

[David G. Johnston]

On Wed, Oct 7, 2015 at 2:58 PM, Steve Pribyl 
wrote:

> Good Afternoon,
>
> We are in a bit of pickle with this as I think some of the issues we may
> be having could possible be caused by being backup mode for months.
>
> What can I check or do to make sure the db is not in backup mode and under
> what circumstances/how can I remove that backup.old file?
>

um...

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

pg_is_in_backup()

David J.

Re: [GENERAL] backup.old

[Steve Pribyl]

Great, dur(rtfm), so is it save to delete the backup.old, if the db is not in 
backup mode.


Steve Pribyl
Sr. Systems Engineer
steve.pri...@akunacapital.com
Desk: 312-994-4646




From: David G. Johnston 
Sent: Wednesday, October 7, 2015 2:12 PM
To: Steve Pribyl
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] backup.old

On Wed, Oct 7, 2015 at 2:58 PM, Steve Pribyl 
> wrote:
Good Afternoon,

We are in a bit of pickle with this as I think some of the issues we may be 
having could possible be caused by being backup mode for months.

What can I check or do to make sure the db is not in backup mode and under what 
circumstances/how can I remove that backup.old file?

um...

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

pg_is_in_backup()

David J.


[http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 

p: +1 312 994 4646 | m: | f: +1 312 750 1667 | steve.pri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.

Re: [GENERAL] Try to understand VACUUM and its settings

[Jim Nasby]

On 10/5/15 5:00 PM, Michael Chau wrote:

So, do I need to run vacuum freeze on those tables? Also, if
autovacuum_freeze_max_age is commented, does it still mean that the
default is 200M?


Yes, and unless you're running a very high transaction rate you probably 
don't need to mess with it. Likewise you normally don't need to 
explicitly freeze, though it's not a bad idea to do so after a large 
data load. Just make sure that there's no open transactions that were 
started before the data load transaction when you run the VACUUM.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] md5(large_object_id)

[Jim Nasby]

On 10/7/15 10:34 AM, Karsten Hilbert wrote:

Still, I'd welcome a native, streaming md5(loid) which is
bound to be more optimized by design.


It would be nice if we had an interface to TOAST that allowed for 
streaming (well, really chunking) data to a function. That wouldn't help 
in this particular case, but it would significantly expand the 
usefulness of a streaming version of md5 and all the other hash operators.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] How to drop user if objects depend on it

[Andrus]

A little further review shows that DROP OWNED is the way to get rid of
leftover privileges.  So in general you need to do REASSIGN OWNED to move
the ownership of objects, then DROP OWNED to get rid of privileges granted
on non-owned objects, before you can drop a role.


I tried this in database mydb using script below but still got error

ERROR:  role "vantaa" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb

How to drop role?

Andrus.

set local role admin; -- admin is not superuser but is member of 
mydb_owner

CREATE ROLE vantaa;
grant mydb_owner to vantaa;

revoke all on all tables in schema public,firma1 from vantaa cascade;
revoke all on all sequences in schema public,firma1 from vantaa cascade;
revoke all on database mydb  from vantaa cascade;
revoke all on all functions in schema public,firma1 from vantaa cascade;
revoke all on schema public,firma1 from vantaa cascade;
revoke mydb_owner  from vantaa cascade;
ALTER ROLE vantaa inherit NOCREATEROLE NOCREATEDB NOLOGIN;

grant all on all tables in schema public,firma1 to vantaa;
grant all on all sequences in schema public,firma1 to vantaa;
grant all on database mydb  to vantaa;
grant all on schema public,firma1 to vantaa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;
revoke all on  kasutaja,kaspriv,logifail from vantaa cascade;
grant select on kaspriv,kasutaja to vantaa;
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup)
on kasutaja to vantaa;
grant insert on logifail to vantaa;

GRANT vantaa TO admin;
reassign owned by vantaa to mydb_owner;
drop owned by vantaa;
drop user vantaa; 




--
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] using postgresql for session

[Rob Sargent]

On 10/07/2015 10:53 AM, Bill Moran wrote:

On Wed, 7 Oct 2015 09:58:04 -0600
"john.tiger"  wrote:


has anyone used postgres jsonb for holding session ?  Since server side
session is really just a piece of data, why bother with special
"session" plugins and just use postgres to hold the data and retrieve it
with psycopg2 ?  Maybe use some trigger if session changes?We are
using python Bottle with psycopg2 (super simple, powerful combo) - are
we missing something magical about session plugins ?

Nothing that I'm aware of. I've worked on large projects that keep the
session data in a Postgres table with great success.

Previous job gave up on mongo and switched to postgres since it was 
faster, more reliable.

Re: [GENERAL] How to drop user if objects depend on it

[Tom Lane]

I wrote:
> "Andrus"  writes:
>> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO 
>> vantaa;

> I am not sure that REASSIGN OWNED will get rid of default-privilege
> specifiers --- you might have to reverse this step separately.

A little further review shows that DROP OWNED is the way to get rid of
leftover privileges.  So in general you need to do REASSIGN OWNED to move
the ownership of objects, then DROP OWNED to get rid of privileges granted
on non-owned objects, before you can drop a role.

This is documented, but only in passing in the REASSIGN OWNED man page.
I think it needs to be explained more prominently.  Will see about making
that happen.

regards, tom lane


-- 
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] How to drop user if objects depend on it

[Andrus]

ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.
In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.


I tried as superuser:

reassign owned by farukkugay to postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma2 revoke all ON TABLES from 
farukkugay;

drop user farukkugay ;

but got error

ERROR: role "farukkugay" cannot be dropped because some objects depend on it
SQL state: 2BP01
Detail: privileges for schema public

How to to delete user ?

Andrus. 




--
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] Issues with german locale on CentOS 5,6,7

[Peter Geoghegan]

On Wed, Oct 7, 2015 at 6:25 AM, Tom Lane  wrote:
> The only real way out of such a situation is to REINDEX affected indexes.
> Refusing to start the server not only doesn't contribute to a solution,
> but makes it impossible to fix manually.

I agree that that would be almost as bad as carrying on, because there
is no reason to think that the locale thing can easily be rolled back.
That was my point, in fact.

-- 
Regards,
Peter Geoghegan


-- 
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] using postgresql for session

[Bill Moran]

On Wed, 7 Oct 2015 09:58:04 -0600
"john.tiger"  wrote:

> has anyone used postgres jsonb for holding session ?  Since server side 
> session is really just a piece of data, why bother with special 
> "session" plugins and just use postgres to hold the data and retrieve it 
> with psycopg2 ?  Maybe use some trigger if session changes?We are 
> using python Bottle with psycopg2 (super simple, powerful combo) - are 
> we missing something magical about session plugins ?

Nothing that I'm aware of. I've worked on large projects that keep the
session data in a Postgres table with great success.

-- 
Bill Moran


-- 
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] How to drop user if objects depend on it

[Adrian Klaver]

On 10/07/2015 09:50 AM, Andrus wrote:

ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.
In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.


I tried as superuser:

reassign owned by farukkugay to postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma2 revoke all ON TABLES
from farukkugay;
drop user farukkugay ;

but got error

ERROR: role "farukkugay" cannot be dropped because some objects depend
on it
SQL state: 2BP01
Detail: privileges for schema public



Above you revoked DEFAULT PRIVILEGES which applies to objects created in 
future. You still probably have PRIVILEGES assigned to farukkugay on the 
public schema. So in psql do:


\dn+ public



How to to delete user ?

Andrus.



--
Adrian Klaver
adrian.kla...@aklaver.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: Ynt: [GENERAL] How to drop user if objects depend on it

[Jerry Sievers]

Neslisah Demirci  writes:

> Hi ,
>
> REASSIGN OWNED -- change the ownership of database objects owned by a 
> database role.
>
> REASSIGN OWNED BY old_role [, ...] TO new_role
>
> You can create a new role then you just assign database objects depend on old 
> role.
> REASSIGN owned by old_role to new_role;
>
> Then
>
> DROP old_role;
>
> Is this helpful?

It might be if were accurate :-)

Permissions are not reassignable.

drop owned by foo_role;

Sometimes to be on the safe side, just in case foo_role did own objects
that you'd rather not drop...

create role foo_orphaned_objects_role;
reassign owned by foo_role to foo_orphaned_objects_role;
drop owned by foo_role
drop role foo_role;

Note that you may have to repeat this for each DB in a given cluster if
foo_role owns things or is direct grant recipient.

>
> Neslisah.
>
> 
> G?nderen: pgsql-general-ow...@postgresql.org 
>  ad?na Andrus 
> G?nderildi: 07 Ekim 2015 ?ar?amba 13:42
> Kime: pgsql-general
> Konu: [GENERAL] How to drop user if objects depend on it
>  
> Hi!
>  
> Database idd owner is role idd_owner
> Database has 2 data schemas: public and firma1.
> User may have directly or indirectly assigned rights in this database and 
> objects.
> User is not owner of any object. It has only rights assigned to objects.
>  
> How to drop such  user ?
>  
> I tried
>  
> revoke all on all tables in schema public,firma1 from "vantaa" cascade;
> revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
> revoke all on database idd from "vantaa" cascade;
> revoke all on all functions in schema public,firma1 from "vantaa" cascade;
> revoke all on schema public,firma1 from "vantaa" cascade;
> revoke idd_owner from "vantaa" cascade;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES 
> from "vantaa";
> DROP ROLE if exists "vantaa"
>  
> but got error
>
> role "vantaa" cannot be dropped because some objects depend on it
> DETAIL:  privileges for schema public
>  
> in statement
>  
> DROP ROLE if exists "vantaa"
>  
> How to fix this so that user can dropped ?
>  
> How to create sql or plpgsql method which takes user name as parameter and 
> drops this user in all cases without dropping data ?
> Or maybe there is some command or simpler commands in postgres ?
>
> Using Postgres 9.1+
> Posted also in
>  
> http://stackoverflow.com/questions/32988702/how-to-drop-user-in-all-cases-in-postgres
>
> [apple-touch-icon] sql - How to drop user in postgres if it has depending 
> objects - Stack Overflow  
> 
>Database idd owner is role idd_owner Database has 2 data 
> schemas: public and firma1. User may have directly or indirectly assigned 
> rights in this
>database and objects. User is not owner of any ob...   
>   
> 
>Devam?n? okuyun... 
>   
> 
>
> Andrus.
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
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] postgres standby won't start

[Ramalingam, Sankarakumar]

Hi Adrian,
Thanks for your prompt response. I used pg_basebackup to backup my source 
server. I am starting this exercise on our QA side so I can do a dry run before 
hitting production. Would I be able to restore that backup on my target 
standby? If yes, would you guide me to the steps. Thanks a lot for your 
invaluable suggestions overall to this forum.



Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Monday, October 05, 2015 7:35 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:
> Thanks Adrian.
>
> My primary is doing  fine. Only the standby. I am noticing it after a 
> while..my Bad!!
> Primary setting
> #-
> -
> # REPLICATION
> #-
> -
>
> max_wal_senders = 5
> wal_sender_delay = 1s
> wal_keep_segments = 512
> vacuum_defer_cleanup_age = 20
> hot_standby = on
>
> Since I am unable to bring standby up, would the pg_basebackup help me out 
> here for a complete sync? Thanks again.

The problem is that the WAL files got recycled on the primary and are no longer 
available there, which is why the standby is failing. If you where archiving 
the WAL files somewhere, you could fetch the missing WAL files from there. If 
that is not the case then you will need to rebuild the standby to the current 
state(more or less) of the primary. The simplest way is to use pg_backup, but 
that means starting with either no data directory or an empty one on the 
standby. The docs page steps you through the procedure.

>
>
>
> Thanks
> Kumar Ramalingam
> X6015288
>
>



--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.



-- 
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] using postgresql for session

[Adrian Klaver]

On 10/07/2015 08:58 AM, john.tiger wrote:

has anyone used postgres jsonb for holding session ?  Since server side
session is really just a piece of data, why bother with special
"session" plugins and just use postgres to hold the data and retrieve it
with psycopg2 ?


That is how Django does it. It uses the django_session table.

 Maybe use some trigger if session changes?

That would seem to the tricky part, determining when a session changes.

  We are

using python Bottle with psycopg2 (super simple, powerful combo) - are
we missing something magical about session plugins ?


What sort of plugin are you talking about?







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


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