Re: [GENERAL] Best practices for aggregate table design

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 dimensio

Re: [GENERAL] postgres standby won't start

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.

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

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

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

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

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

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

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

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. >> >

Re: [GENERAL] postgres standby won't start

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@po

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

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 eithe

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

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

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 manual

Re: [GENERAL] postgres standby won't start

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 Tha

Re: [GENERAL] md5(large_object_id)

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 would

Re: [GENERAL] postgres standby won't start

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

Re: [GENERAL] Best practices for aggregate table design

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.

Re: [GENERAL] backup.old

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

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

"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

Re: [GENERAL] backup.old

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

Re: [GENERAL] backup.old

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-

Re: [GENERAL] Best practices for aggregate table design

>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 d

[GENERAL] PostGIS 2.2.0 Released

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_3D

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 succe

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

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,

Re: [GENERAL] backup.old

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, Octobe

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 >

Re: [GENERAL] backup.old

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? T

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

"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.

Re: [GENERAL] using postgresql for session

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 r

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

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 m

Re: [GENERAL] md5(large_object_id)

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 partic

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

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. Likewis

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

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 curr

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 s

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

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

Re: [GENERAL] using postgresql for session

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.

Re: [GENERAL] postgres standby won't start

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

Re: [GENERAL] using postgresql for session

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

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

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,

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

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 memb

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

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

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

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

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

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' E

[GENERAL] using postgresql for session

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 Bot

Re: [GENERAL] md5(large_object_id)

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 stri

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

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

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

"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 p

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

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 object

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

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,

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

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 lo

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

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 y

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

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

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

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

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

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 NOSUPE

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

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 object

Re: [GENERAL] md5(large_object_id)

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 ce

Re: [GENERAL] md5(large_object_id)

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

Re: [GENERAL] md5(large_object_id)

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 pos

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

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

Re: [GENERAL] md5(large_object_id)

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 wo

[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

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

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

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

--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 colla

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

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 li

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

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 Postg