Re: [GENERAL] why is pg_dump so much smaller than my database?

[John R Pierce]

On 03/28/12 10:32 PM, Carson Gross wrote:
I've got a pretty big database (~30 gigs) and when I do a pg_dump, it 
ends up only being 2 gigs.


The database consists mainly of one very large table (w/ a few varchar 
columns) which, according to pg_relation_size() is 10 gigs 
and pg_total_relation_size() is 26 gigs (we need to drop some indexes 
there.)


I'm just trying to get my head around the pg_dump being an order of 
magnitude smaller than the darned database itself.  I would thing that 
the db would offer more efficient encoding for a lot of stuff vs. an 
ascii file.




its quite possible your table has a lot of free tuples scattered through 
it as a result of updates or deletes.   vacuum makes these available for 
reuse but does NOT free the disk space.  ditto, your indexes might be 
very bloated, a reindex may significantly shrink them


if you can afford some application downtime, you may consider running 
CLUSTER on that table, it will copy all the active tuples of the table 
to new file space, and free the old, and also does the reindex 
operation.   I would vacuum the table first, after ensuring there aren't 
any old active transactions ('IDLE IN TRANSACTION' status in 
pg_stat_activity).   Note that cluster takes an exclusive lock on the 
table, this is why I said you need some application downtime.


you don't say what version you're running, older versions had more 
problems with bloating indexes than newer ones.








--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] why is pg_dump so much smaller than my database?

[Alban Hertroys]

On 29 March 2012 09:11, John R Pierce  wrote:
> On 03/28/12 10:32 PM, Carson Gross wrote:
>>
>> I've got a pretty big database (~30 gigs) and when I do a pg_dump, it ends
>> up only being 2 gigs.

I suppose you're talking about a plain text dump here? A compressed
dump would likely[*] be quite a bit smaller than your database.

And as John already says, deleted tuples do not get dumped, neither do
index contents. Any fill-factors defined on tables or indexes would
also account for a difference in size.

*: This does depend on what data you store, of course. A database that
mainly consists of stored JPEG images, for example, wouldn't compress
much. A database with the same text data over and over would compress
immensely.
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
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] system catalog privilege and create privilege ??? how to control them?? thanks

[Albe Laurenz]

leaf_yxj wrote:
> For oracle, the normal user can't see all the system catalog. but for
> postgresql, it looks like all the user can see the system catalog.
Should
> we limit the user read privilege to system catalog?

You can try that, but things may break in unexpected ways.
For example, psql's utility commands will probably stop working.
I would test any such change thoroughly.

Not all system catalogs are visible for everybody, tables and views
containing passwords for example can only be read by superusers.

PostgreSQL has fewer restrictions on reading system catalogs than
Oracle.
I can see how a seasoned Oracle DBA might feel uneasy if everybody
can find out all user names on the database cluster.

> In oracle, the system privilege has create table, create view,create
> function.  For postgresql database, how to control the user who only
can
> create table but can't create view. Based on the test I did, once the
user
> has the create privilege on the schema, the user will have any create
> privilege on that schema. In postgresql, Rule is used to control that
???
> very confused!

PostgreSQL's permission system is different from Oracle's.
Oracle has a lot of "system privileges" which PostgreSQL does not
have or need.

In Oracle, every user automatically has a schema of the same name
and there are no permissions on schema basis.  So you need system
privileges if you want to keep users from creating objects.

In PostgreSQL you can use schema permissions.
True, as soon as you have CREATE on a schema, you can create any
kind of object there.  That is, any kind of object that does not
depend on anything else.
To create a function, you need the USAGE privilege on the
procedural function.  You can revoke this right from PUBLIC and
only give it to the users you want.
To create a trigger, you need the TRIGGER privilege on the
table involved and the EXECUTE privilege on the trigger function.

So you see, most of what Oracle handles with system privileges is
handled with object privileges in PostgreSQL.  And you usually
can assign permissions in a finer granularity that way.

Of course it is confusing at first, but once you understand
PostgreSQL's permission system, there are few meaningful things
that you cannot achieve with it.
What's the use case for granting somebody CREATE TABLE, but
not CREATE VIEW?

Yours,
Laurenz Albe

-- 
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] Query regarding submission on To Do item for psql client "psql : Allow processing of multiple -f (file) options "

[Albe Laurenz]

Vikash3 S wrote:
> Would like to submit patch on this TO Do list item which deals with
psql client, "psql : Allow
> processing of multiple -f (file) options ".
> 
> The code base which I am working on is from postgres 9.1.3 release.
> But when I diff the code base from git repository, the changes are far
different from the postgres
> 9.1.3 release source code directory.
> 
> Can I submit the patch(created out of git diff) w.r.t my working
directory associated with git after
> pulling the changes to my working directory, this changes are done and
tested against postgres 9.1.3
> release source code directory.
> 
> Shall I go ahead and submit patch for it to
pgsql-hack...@postgresql.org.
> Please advice on the way to go ahead.

There is a lot of helpful information on
http://wiki.postgresql.org/wiki/Development_information
Answers to your questions will be found in "Submitting a Patch" and the
"Developer FAQ",
but you should acquaint yourself with the whole development process.

Yours,
Laurenz Albe

-- 
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 tell if server is in backup mode?

[Gabriele Bartolini]

Hi Toby,

Il 28/03/12 09:14, Toby Corkindale ha scritto:
Is there any function like pg_is_in_backup() to tell if the mode has 
been enabled?


Currently, there's no such a function. In general we simply check if a 
'backup_label' file exists in PGDATA. However, it could be a good idea 
to add it as admin function (I will have a think about it and possibly 
come up with a patch).


Ciao,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it


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

Re: [GENERAL] PG Log

[Albe Laurenz]

Arvind Singh wrote:
> I have queries regarding columns in Postgres CSV Log.
> 
> Following is a sample Logline
>

> 2012-03-28 19:25:47.968
IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,6,"SET",
2012-
> 03-28 19:25:47 IST,2/0,0,LOG,0,"QUERY STATISTICS","! system usage
stats:
> ! 0.047000 elapsed 0.00 user 0.00 system sec
> ! [0.078125 user 0.031250 sys total]","Select * from stock_apals"
> ,,"ShowUsage, .\src\backend\tcop\postgres.c:4305",""
>

> 
> I am aware of all the data segments except the following
>

> "! system usage stats:
> ! 0.047000 elapsed 0.00 user 0.00 system sec
> ! [0.078125 user 0.031250 sys total]",
>

> 
> What do the number mean, it seems to appear only with a Logline for
SQL statements

These lines are emitted if you set log_statement_stats to "on".

They contain execution statistics for the query. The "user" and "sys"
times are acquired by the getrusage(2) or times(2) system call,
depending
on your operating system (on Windows, GetProcessTimes is used).
You can look at the man page for your system for details.

The values you see are:
wall time, CPU user time and kernel CPU time it took to execute the
query.
The values in brackets are the values accumulated for this database
session.

Yours,
Laurenz Albe

-- 
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 tell if server is in backup mode?

[Gabriele Bartolini]

Hi Toby,

Il 29/03/12 10:46, Gabriele Bartolini ha scritto:
Currently, there's no such a function. In general we simply check if a 
'backup_label' file exists in PGDATA. However, it could be a good idea 
to add it as admin function (I will have a think about it and possibly 
come up with a patch).
My bad (thanks Carlo Ascani for pointing this out to me). Gilles Darold 
just proposed such a patch and I looked into the commitfest website: 
https://commitfest.postgresql.org/action/patch_view?id=803


I just added myself as a reviewer for PostgreSQL 9.3.

Cheers,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it


--
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] user get notification when postgresql database updated

[Albert]

Thanks for your response!

it a browser based application. so would you advice me about the best way to
poll the database for notifications ? 

I've been read about DB triggers but still can't tell if it will help me.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/user-get-notification-when-postgresql-database-updated-tp5600187p5603197.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

[GENERAL] Why checkpoint_timeout had maximum value of 1h?

[Maxim Boguk]

Hi all,

Is there any real reason why checkpoint_timeout limited to 1hour?

In my case I have some replicas with WAL on SAS raid and PGDATA on SSD with
limited write endurance.
And I don't worry about possible long time recovery after power failure in
that case.
Whats more working dataset fill in shared buffers, so almost no dirty
buffers evictions by bgwriter or backends happened.

In that case having checkpoint_timeout=10hour could reduce amout of writes
on SSD  by factor of 10, and increase planned ssd lifetime by the same
amount.

I would like to have ability to set checkpoint_timeout=high value
and (whats even better) checkpoint_timeout=0 - in that case checkpoint
happen when all checkpoint_segments were used.

Is there any serious drawbacks in that idea?

Is it safe to increase that limit in source and rebuild database?  (9.0 and
9.1 case)

-- 
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim.bo...@gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
"If they can send one man to the moon... why can't they send them all?"

МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."

Re: [GENERAL] could not read block... how could I identify/fix

[Vick Khera]

On Wed, Mar 28, 2012 at 6:31 PM, Naoko Reeves  wrote:
> Do you think this should be the next step I might take?
> Could you give me an advice of how I could identify corrupted error.

It seems to me that since you can successfully dump the table (I
assume you validated the data was all there somehow), you should go
ahead and dump your whole DB, delete the current one, create it again,
then restore it from scratch.

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

[GENERAL] More PG Log

[Arvind Singh]

Oh, thankx for the answer on PG Log. 
 
our postgres is on windows , it is version 9.0.5. There are a variety of 
applications, like CRM , Stockrelated 
and now there is a online game based on it.
 
We are providing application development, statistics, datametrics support . For 
simple application and user settings
we use SqlLite and main application is on Postgres
 
PG Log is our only window to all the activies on DB Server and therefore it 
becomes very important especially to track 
abnormal query or activities that are causing frequent errors. at any time we 
have aroung 10k entries waiting to be analyzed
for which we are building a customer application in c sharp, that matches our 
keywords.
 
For which, 
 
Query 1
---
do we have a standard list of following Log Codes
- Command_tag ex. IDLE, SELECT ..
- error_severity , ex. FATAL, LOG .. 
- sql_state_code , ex. 0, 08P01 ..
 
 
Query 2
--

I have my CSV Log with lot of occurances of a certain Log select statement.  
-
2012-03-28 19:25:48.015 
IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,7,"idle",2012-03-28
 19:25:47 IST,2/98,0,LOG,0,"statement: SELECT typname, oid FROM pg_type 
WHERE typname IN ('oidvector', '_oidvector', 'unknown', '_unknown', 
'refcursor', '_refcursor', 'char', '_char', 'bpchar', '_bpchar', 'varchar', 
'_varchar', 'text', '_text', 'name', '_name', 'bytea', '_bytea', 'bit', '_bit', 
'bool', '_bool', 'int2', '_int2', 'int4', '_int4', 'int8', '_int8', 'oid', 
'_oid', 'float4', '_float4', 'float8', '_float8', 'numeric', '_numeric', 
'inet', '_inet', 'money', '_money', 'point', '_point', 'lseg', '_lseg', 'path', 
'_path', 'box', '_box', 'circle', '_circle', 'polygon', '_polygon', 'uuid', 
'_uuid', 'xml', '_xml', 'interval', '_interval', 'date', '_date', 'time', 
'_time', 'timetz', '_timetz', 'timestamp', '_timestamp', 'abstime', '_abstime', 
'timestamptz', '_timestamptz')""exec_simple_query, 
.\src\backend\tcop\postgres.c:900",""
-
 
Is is a performance concern. ? 
is there anything that i can do to keep this statement from recurring.

 
 
Regards
arvind 
 
 

 

> Subject: RE: [GENERAL] PG Log
> Date: Thu, 29 Mar 2012 10:49:20 +0200
> From: laurenz.a...@wien.gv.at
> To: arvin...@hotmail.com; pgsql-general@postgresql.org
> 
> Arvind Singh wrote:
> > I have queries regarding columns in Postgres CSV Log.
> > 
> > Following is a sample Logline
> >
> 
> > 2012-03-28 19:25:47.968
> IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,6,"SET",
> 2012-
> > 03-28 19:25:47 IST,2/0,0,LOG,0,"QUERY STATISTICS","! system usage
> stats:
> > ! 0.047000 elapsed 0.00 user 0.00 system sec
> > ! [0.078125 user 0.031250 sys total]","Select * from stock_apals"
> > ,,"ShowUsage, .\src\backend\tcop\postgres.c:4305",""
> >
> 
> > 
> > I am aware of all the data segments except the following
> >
> 
> > "! system usage stats:
> > ! 0.047000 elapsed 0.00 user 0.00 system sec
> > ! [0.078125 user 0.031250 sys total]",
> >
> 
> > 
> > What do the number mean, it seems to appear only with a Logline for
> SQL statements
> 
> These lines are emitted if you set log_statement_stats to "on".
> 
> They contain execution statistics for the query. The "user" and "sys"
> times are acquired by the getrusage(2) or times(2) system call,
> depending
> on your operating system (on Windows, GetProcessTimes is used).
> You can look at the man page for your system for details.
> 
> The values you see are:
> wall time, CPU user time and kernel CPU time it took to execute the
> query.
> The values in brackets are the values accumulated for this database
> session.
> 
> Yours,
> Laurenz Albe
  

Re: [GENERAL] could not read block... how could I identify/fix

[Vick Khera]

On Wed, Mar 28, 2012 at 6:31 PM, Naoko Reeves  wrote:
> Version: "PostgreSQL 8.4.6 on

Oh, and also upgrade to 8.4.11 to ensure you do not have any known
data loss bugs.

-- 
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] More PG Log

[Albe Laurenz]

Arvind Singh wrote:
> Query 1
> ---
> do we have a standard list of following Log Codes
> - Command_tag ex. IDLE, SELECT ..

See the source code for your version:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/
tcop/utility.c;h=ec36644a492ab69d5306b52294daab0599f332fe;hb=8522403c5cd
2351a1292b868a85aeec0aab5f2b3
Look for the function CreateCommandTag, it contains all the strings.

> - error_severity , ex. FATAL, LOG ..

See
http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html#RU
NTIME-CONFIG-SEVERITY-LEVELS

> - sql_state_code , ex. 0, 08P01 ..

See
http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html#ERRCODE
S-TABLE


> Query 2
> --
> 
> I have my CSV Log with lot of occurances of a certain Log select
statement.
>

-
> 2012-03-28 19:25:48.015
IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,7,"idle"
,2012-
> 03-28 19:25:47 IST,2/98,0,LOG,0,"statement: SELECT typname, oid
FROM pg_type WHERE typname IN
> ('oidvector', '_oidvector', 'unknown', '_unknown', 'refcursor',
'_refcursor', 'char', '_char',
> 'bpchar', '_bpchar', 'varchar', '_varchar', 'text', '_text', 'name',
'_name', 'bytea', '_bytea',
> 'bit', '_bit', 'bool', '_bool', 'int2', '_int2', 'int4', '_int4',
'int8', '_int8', 'oid', '_oid',
> 'float4', '_float4', 'float8', '_float8', 'numeric', '_numeric',
'inet', '_inet', 'money', '_money',
> 'point', '_point', 'lseg', '_lseg', 'path', '_path', 'box', '_box',
'circle', '_circle', 'polygon',
> '_polygon', 'uuid', '_uuid', 'xml', '_xml', 'interval', '_interval',
'date', '_date', 'time', '_time',
> 'timetz', '_timetz', 'timestamp', '_timestamp', 'abstime', '_abstime',
'timestamptz',
> '_timestamptz')""exec_simple_query,
.\src\backend\tcop\postgres.c:900",""
>

-
> 
> Is is a performance concern. ?
> is there anything that i can do to keep this statement from recurring.

It will probably cause a sequential scan, but the table is not very big
unless
you have a lot of table or type definitions.

PostgreSQL does not issue such a query as far as I know.
You should figure out what in your application stack causes that.

If the query is issued once per session start, that shouldn't be a
problem
unless you open and close sessions all the time (in which case your
design ist probably bad and you should consider a connection pool).

Yours,
Laurenz Albe

-- 
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] why is pg_dump so much smaller than my database?

[Carson Gross]

Interesting.  Is there a perf hit to having a big file on disk?  My
understanding is that the primary thing that really matters is keeping your
active set in memory.

This is on Postgres 9.0.x, running on Heroku/ec2.

We do have extremely compressible data so it may be that the dump is
compressed: I'm downloading it now to check.

Thanks for the replies,
Carson

On Thu, Mar 29, 2012 at 12:11 AM, John R Pierce  wrote:

> On 03/28/12 10:32 PM, Carson Gross wrote:
>
>> I've got a pretty big database (~30 gigs) and when I do a pg_dump, it
>> ends up only being 2 gigs.
>>
>> The database consists mainly of one very large table (w/ a few varchar
>> columns) which, according to pg_relation_size() is 10 gigs and
>> pg_total_relation_size() is 26 gigs (we need to drop some indexes there.)
>>
>> I'm just trying to get my head around the pg_dump being an order of
>> magnitude smaller than the darned database itself.  I would thing that the
>> db would offer more efficient encoding for a lot of stuff vs. an ascii file.
>>
>>
> its quite possible your table has a lot of free tuples scattered through
> it as a result of updates or deletes.   vacuum makes these available for
> reuse but does NOT free the disk space.  ditto, your indexes might be very
> bloated, a reindex may significantly shrink them
>
> if you can afford some application downtime, you may consider running
> CLUSTER on that table, it will copy all the active tuples of the table to
> new file space, and free the old, and also does the reindex operation.   I
> would vacuum the table first, after ensuring there aren't any old active
> transactions ('IDLE IN TRANSACTION' status in pg_stat_activity).   Note
> that cluster takes an exclusive lock on the table, this is why I said you
> need some application downtime.
>
> you don't say what version you're running, older versions had more
> problems with bloating indexes than newer ones.
>
>
>
>
>
>
>
> --
> john r pierceN 37, W 122
> santa cruz ca mid-left coast
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

[GENERAL] Managing two sets of data in one database

[Jonathan Bartlett]

I have a database which contains two primary sets of data:

1) A large (~150GB) dataset.  This data set is mainly static.  It is
updated, but not by the users (it is updated by our company, which provides
the data to users).  There are some deletions, but it is safe to consider
this an "add-only" database, where only new records are created.
2) A small (~10MB but growing) dataset.  This is the user's data.  It
includes many bookmarks (i.e. foreign keys) into data set #1.  However, I
am not explicitly using any referential integrity system.

Also, many queries cross the datasets together.

Now, my issue is that right now when we do updates to the dataset, we have
to make them to the live database.  I would prefer to manage data releases
the way we manage software releases - have a staging area, test the data,
and then deploy it to the users.  However, I am not sure the best approach
for this.  If there weren't lots of crossover queries, I could just shove
them in separate databases, and then swap out dataset #1 when we have a new
release.

Does anyone have any ideas?

Thanks,

Jon

Re: [GENERAL] Managing two sets of data in one database

[John R Pierce]

On 03/29/12 9:43 AM, Jonathan Bartlett wrote:
1) A large (~150GB) dataset.  This data set is mainly static.  It is 
updated, but not by the users (it is updated by our company, which 
provides the data to users).  There are some deletions, but it is safe 
to consider this an "add-only" database, where only new records are 
created.
2) A small (~10MB but growing) dataset.  This is the user's data.  It 
includes many bookmarks (i.e. foreign keys) into data set #1. 
 However, I am not explicitly using any referential integrity system.


by 'dataset' do you mean table, aka relation ?

by 'not using any referential integrity', do you mean, you're NOT using 
foreign keys ('REFERENCES table(field)' in your table declaration ?




Also, many queries cross the datasets together.



by 'cross', do you mean JOIN  ?

Now, my issue is that right now when we do updates to the dataset, we 
have to make them to the live database.  I would prefer to manage data 
releases the way we manage software releases - have a staging area, 
test the data, and then deploy it to the users.  However, I am not 
sure the best approach for this.  If there weren't lots of crossover 
queries, I could just shove them in separate databases, and then swap 
out dataset #1 when we have a new release.




you can't JOIN data across relations(tables) in different databases.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] Managing two sets of data in one database

[Jonathan Bartlett]

>
>
> by 'dataset' do you mean table, aka relation ?'
>

It's a group of tables.


> by 'not using any referential integrity', do you mean, you're NOT using
> foreign keys ('REFERENCES table(field)' in your table declaration ?


Correct.

Also, many queries cross the datasets together.
>>
>>
> by 'cross', do you mean JOIN  ?


There are joins, subselects, and similar queries which cross the datasets.


>  Now, my issue is that right now when we do updates to the dataset, we
>> have to make them to the live database.  I would prefer to manage data
>> releases the way we manage software releases - have a staging area, test
>> the data, and then deploy it to the users.  However, I am not sure the best
>> approach for this.  If there weren't lots of crossover queries, I could
>> just shove them in separate databases, and then swap out dataset #1 when we
>> have a new release.
>>
>>
> you can't JOIN data across relations(tables) in different databases.
>
>
Right.  That's the reason I asked on the list.  I didn't know if there is a
good way of managing this sort of data.  If I could just have two different
databases, I would have done that a while ago.  I didn't know if someone
had a similar situation and what kind of solution they used for it.  Right
now, both datasets are in the same database.  But that means I can't do
releases of the static dataset, and instead, when the company updates the
database, we have to make the updates directly on the live database.  I'm
trying to avoid that and do releases, and I am seeing if anyone knows of a
good approach given the constraints.

Jon

Re: [GENERAL] Managing two sets of data in one database

[Tom Molesworth]

Hi Jonathan,

On 29/03/12 19:01, Jonathan Bartlett wrote:


Now, my issue is that right now when we do updates to the
dataset, we have to make them to the live database.  I would
prefer to manage data releases the way we manage software
releases - have a staging area, test the data, and then deploy
it to the users.  However, I am not sure the best approach for
this.  If there weren't lots of crossover queries, I could
just shove them in separate databases, and then swap out
dataset #1 when we have a new release.


you can't JOIN data across relations(tables) in different databases.


Right.  That's the reason I asked on the list.  I didn't know if there 
is a good way of managing this sort of data.  If I could just have two 
different databases, I would have done that a while ago.  I didn't 
know if someone had a similar situation and what kind of solution they 
used for it.  Right now, both datasets are in the same database.  But 
that means I can't do releases of the static dataset, and instead, 
when the company updates the database, we have to make the updates 
directly on the live database.  I'm trying to avoid that and do 
releases, and I am seeing if anyone knows of a good approach given the 
constraints.




Have you considered using views in the queries instead of hitting the 
base tables directly? You could then load the releases into a different 
schema (so instead of select * from mytable, you have a view which does 
select * from release_20110329.mytable, for example) or use different 
table names for each release (live_*, test_*, beta_* maybe). Switching 
between releases should be fast (and atomic), but everything would still 
be within the same database so you'd be able to get to all the data you 
need.


cheers,

Tom

[GENERAL] Move Tables From One Database to Another

[Rich Shepard]

   I'm storing vector map attribute data in postgres tables and somehow
managed to create two databases (of similar names) rather than one. I want
to combine the two.

   For tables that exist in the one database I want to eliminate, I thought
to use pg_dump to create .sql files, then use pg_restore to add the table to
the other database. Did this for one table (with 4201 rows), but 'pg_restore
-d database_name -t table_name' appears to not complete; it seems to have
hung up somewhere. While I see nothing specific in the output file or the
pg_restore man page this must not be the proper approach.

  Also, I need suggestions on how to combine tables that exist in both
databases by adding rows from the source database not in the target database
and modifying rows that differ.

   As I'm not a professional or full-time DBA I'm probably missing really
simple syntax and approaches. Your advice will be appreciated.

Rich


--
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] Move Tables From One Database to Another

[Gabriele Bartolini]

Hi Rich,

Il 29/03/12 21:10, Rich Shepard ha scritto:
   For tables that exist in the one database I want to eliminate, I 
thought
to use pg_dump to create .sql files, then use pg_restore to add the 
table to
the other database. Did this for one table (with 4201 rows), but 
'pg_restore

-d database_name -t table_name' appears to not complete; it seems to have
hung up somewhere. While I see nothing specific in the output file or the
pg_restore man page this must not be the proper approach.
pg_restore works exclusively with custom or tar format archives from 
pg_dump (see -F option).


I suggest that you look at the -l and -L options in pg_restore, which 
allow you to select which dump entries to restore (selective restore).


Cheers,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it


--
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] PANIC: corrupted item pointer

[Jeff Davis]

Hi,

First of all, shut down both servers (you indicated that you have a
replica) and make a full copy of both data directories. At the first
sign of corruption, that's always a good step as long as it's a
practical amount of data (obviously this is more of a challenge if you
have terabytes of data).

On Tue, 2012-03-27 at 11:47 +0200, Janning Vygen wrote:
> Hi,
> 
> I am running postgresql-9.1 from debian backport package
> fsync=on
> full_page_writes=off

That may be unsafe (and usually is) depending on your I/O system and
filesystem. However, because you didn't have any power failures, I don't
think this is the cause of the problem.

> I didn't had any power failures on this server.

These WARNINGs below could also be caused by a power failure. Can you
verify that no power failure occurred? E.g. check uptime, and maybe look
at a few logfiles?

> Now I got this:
> 
> 1. Logfile PANIC
> 
> postgres[27352]: [4-1] PANIC:  corrupted item pointer: offset = 21248,
> size = 16

...

> Then I run "VACUUM rankingentry" and i got:
> kicktipp=# VACUUM rankingentry ;
> WARNING: relation "rankingentry" page 424147 is uninitialized --- fixing
> WARNING: relation "rankingentry" page 424154 is uninitialized --- fixing
> WARNING: relation "rankingentry" page 424155 is uninitialized --- fixing
> WARNING: relation "rankingentry" page 424166 is uninitialized --- fixing
> WARNING: relation "rankingentry" page 424167 is uninitialized --- fixing
> WARNING: relation "rankingentry" page 424180 is uninitialized --- fixing
> VACUUM
> Time: 138736.347 ms
> 

...

> I am worried because i never had any error like this with postgresql. I
> just switched to 9.1 and started to have a hot standby server (WAL
> shipping). Does this error has any relation to this?

Did you get the PANIC and WARNINGs on the primary or the replica? It
might be worth doing some comparisons between the two systems.

Again, make those copies first, so you have some room to explore to find
out what happened.

It seems very unlikely that problems on the master would be caused by
the presence of a replication slave.

> Should I check or exchange my hardware? Is it a hardware problem?

It could be.

> Should I still worry about it?

Yes. The WARNINGs might be harmless if it were a power failure, but you
say you didn't have a power failure. The PANIC is pretty clearly
indicating corruption.

Regards,
Jeff Davis


-- 
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] Move Tables From One Database to Another

[Andy Colson]

On 3/29/2012 2:10 PM, Rich Shepard wrote:

I'm storing vector map attribute data in postgres tables and somehow
managed to create two databases (of similar names) rather than one. I want
to combine the two.

For tables that exist in the one database I want to eliminate, I thought
to use pg_dump to create .sql files, then use pg_restore to add the
table to
the other database. Did this for one table (with 4201 rows), but
'pg_restore
-d database_name -t table_name' appears to not complete; it seems to have
hung up somewhere. While I see nothing specific in the output file or the
pg_restore man page this must not be the proper approach.

Also, I need suggestions on how to combine tables that exist in both
databases by adding rows from the source database not in the target
database
and modifying rows that differ.

As I'm not a professional or full-time DBA I'm probably missing really
simple syntax and approaches. Your advice will be appreciated.

Rich





How many tables are we talking about.  If its a few tables, I'd rename them:

alter table lake rename to lake_old;
... etc

then dump it out and restore into the proper db.

The proper db will now have to tables, lake and lake_old, which you can 
selective update some rows:


update lake
  set foo = (select foo from lake_old where lake_old.id = lake.id)
  where exists (select foo from lake_old where lake_old.id = lake.id);

!! The were exists is very important !!

and insert missing:

insert into lake
select * from lake_old
where not exists (select id from lake_old where lake_old.id = lake.id);

> to use pg_dump to create .sql files, then use pg_restore to add the
> table to 
> it seems to have
> hung up somewhere.


I wonder if a table was in use and pg_restore blocked on the drop table? 
 If you don't mind replacing the entire table, this method should work. 
 But if you want to merge the two tables, I would not go this route.


if you try the restore again, you can do:

ps ax|grep postg
and see what statement its running.  You can also do:

select * from pg_locks where not granted;

and see if anything is blocked.

-Andy

--
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] Move Tables From One Database to Another

[Rich Shepard]

On Thu, 29 Mar 2012, Gabriele Bartolini wrote:


I suggest that you look at the -l and -L options in pg_restore, which
allow you to select which dump entries to restore (selective restore).


Gabriele,

  After sending the message I realized the proper syntax is 'psql -d
database -f table.sql'. That works.

Thanks,

Rich


--
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] Move Tables From One Database to Another

[Rich Shepard]

On Thu, 29 Mar 2012, Andy Colson wrote:


How many tables are we talking about.  If its a few tables, I'd rename them:
alter table lake rename to lake_old;
... etc
then dump it out and restore into the proper db.


Andy,

  This will work just fine. Thanks for the insight.

Rich


--
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] Managing two sets of data in one database

[Jonathan Bartlett]

Tom -

Your suggestion gives me an idea, and I'd like your opinion since I haven't
done much with schemas.

(1) Separate the datasets into different schemas
(2) Use different schema names for different static data releases
(3) For the *company*, we can use a schema search path that includes the
next schema
(4) For the *users*, they can use a schema search path that includes the
released schema

Then, I wouldn't have to modify any code, except to set the schema search
path based on who was connecting.

Does this sound reasonable?

Jon

On Thu, Mar 29, 2012 at 1:26 PM, Tom Molesworth wrote:

>  Hi Jonathan,
>
>
> On 29/03/12 19:01, Jonathan Bartlett wrote:
>
>
>
>>  Now, my issue is that right now when we do updates to the dataset, we
>>> have to make them to the live database.  I would prefer to manage data
>>> releases the way we manage software releases - have a staging area, test
>>> the data, and then deploy it to the users.  However, I am not sure the best
>>> approach for this.  If there weren't lots of crossover queries, I could
>>> just shove them in separate databases, and then swap out dataset #1 when we
>>> have a new release.
>>>
>>>
>>  you can't JOIN data across relations(tables) in different databases.
>>
>>
>  Right.  That's the reason I asked on the list.  I didn't know if there
> is a good way of managing this sort of data.  If I could just have two
> different databases, I would have done that a while ago.  I didn't know if
> someone had a similar situation and what kind of solution they used for it.
>  Right now, both datasets are in the same database.  But that means I can't
> do releases of the static dataset, and instead, when the company updates
> the database, we have to make the updates directly on the live database.
>  I'm trying to avoid that and do releases, and I am seeing if anyone knows
> of a good approach given the constraints.
>
>
> Have you considered using views in the queries instead of hitting the base
> tables directly? You could then load the releases into a different schema
> (so instead of select * from mytable, you have a view which does select *
> from release_20110329.mytable, for example) or use different table names
> for each release (live_*, test_*, beta_* maybe). Switching between releases
> should be fast (and atomic), but everything would still be within the same
> database so you'd be able to get to all the data you need.
>
> cheers,
>
> Tom
>
>

Re: [GENERAL] Managing two sets of data in one database

[Scott Marlowe]

On Thu, Mar 29, 2012 at 2:39 PM, Jonathan Bartlett
 wrote:
> Tom -
>
> Your suggestion gives me an idea, and I'd like your opinion since I haven't
> done much with schemas.
>
> (1) Separate the datasets into different schemas
> (2) Use different schema names for different static data releases
> (3) For the *company*, we can use a schema search path that includes the
> next schema
> (4) For the *users*, they can use a schema search path that includes the
> released schema
>
> Then, I wouldn't have to modify any code, except to set the schema search
> path based on who was connecting.
>
> Does this sound reasonable?

It's pretty much what I was going to suggest.

-- 
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] Managing two sets of data in one database

[Thomas Kellerer]

Jonathan Bartlett wrote on 29.03.2012 22:39:

Your suggestion gives me an idea, and I'd like your opinion since I haven't 
done much with schemas.

(1) Separate the datasets into different schemas
(2) Use different schema names for different static data releases
(3) For the *company*, we can use a schema search path that includes the next 
schema
(4) For the *users*, they can use a schema search path that includes the 
released schema

Then, I wouldn't have to modify any code, except to set the schema search path 
based on who was connecting.


If you create different Postgres users, you can set the search_path per user, 
so you don't have to remember doing that while connecting.


 





--
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] Managing two sets of data in one database

[John R Pierce]

On 03/29/12 2:16 PM, Thomas Kellerer wrote:

Jonathan Bartlett wrote on 29.03.2012 22:39:
Your suggestion gives me an idea, and I'd like your opinion since I 
haven't done much with schemas.


(1) Separate the datasets into different schemas
(2) Use different schema names for different static data releases
(3) For the *company*, we can use a schema search path that includes 
the next schema
(4) For the *users*, they can use a schema search path that includes 
the released schema


Then, I wouldn't have to modify any code, except to set the schema 
search path based on who was connecting.


If you create different Postgres users, you can set the search_path 
per user, so you don't have to remember doing that while connecting. 


heck, the default search_path is $USER,"public"



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

[GENERAL] default value returned from sql stmt

[David Salisbury]

In trying to get an sql stmt to return a default value, I read in the docs..

"The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is 
often used to substitute a default value for null values when data is retrieved for display, for example:

SELECT COALESCE(description, short_description, '(none)') ..."

But I seem to be missing something:

development=# create table t1 ( anum integer );
CREATE TABLE

development=# insert into t1 values ( 2 ), (3);
INSERT 0 2

development=# select * from t1;

 anum
--
2
3

development=# select  coalesce(anum,100) from t1 where anum = 4;
 coalesce
--
(0 rows)

Do I have to resort to PLPGSQL for this?

thanks for any info,

-ds

oh.. running 9.1

--
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] default value returned from sql stmt

[Chris Angelico]

On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury  wrote:
> development=# select  coalesce(anum,100) from t1 where anum = 4;

What you have there is rather different from COALESCE, as you're
looking for a case where the row completely doesn't exist. But you can
fudge it with an outer join.

Untested code:

WITH rowid AS (select 4 as anum) SELECT coalesce(anum,100) FROM rowid
LEFT JOIN t1 ON rowid.anum=t1.anum

However, you may simply want a WHERE [NOT] EXISTS predicate. There may
be other ways of achieving your goal, too.

ChrisA

-- 
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] default value returned from sql stmt

[Pavel Stehule]

Hello

2012/3/30 David Salisbury :
>
> In trying to get an sql stmt to return a default value, I read in the docs..
>
> "The COALESCE function returns the first of its arguments that is not null.
> Null is returned only if all arguments are null. It is often used to
> substitute a default value for null values when data is retrieved for
> display, for example:
> SELECT COALESCE(description, short_description, '(none)') ..."
>
> But I seem to be missing something:
>
> development=# create table t1 ( anum integer );
> CREATE TABLE
>
> development=# insert into t1 values ( 2 ), (3);
> INSERT 0 2
>
> development=# select * from t1;
>
>  anum
> --
>    2
>    3
>
> development=# select  coalesce(anum,100) from t1 where anum = 4;
>  coalesce
> --
> (0 rows)

select anum from t1 where anum = 4
union all select 100 limit 1;

Regards

Pavel

>
> Do I have to resort to PLPGSQL for this?
>
> thanks for any info,
>
> -ds
>
> oh.. running 9.1
>
> --
> 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] default value returned from sql stmt

[David Salisbury]

On 3/29/12 4:26 PM, Chris Angelico wrote:

On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury  wrote:

development=# select  coalesce(anum,100) from t1 where anum = 4;


What you have there is rather different from COALESCE, as you're
looking for a case where the row completely doesn't exist. But you can
fudge it with an outer join.

Untested code:

WITH rowid AS (select 4 as anum) SELECT coalesce(anum,100) FROM rowid
LEFT JOIN t1 ON rowid.anum=t1.anum

However, you may simply want a WHERE [NOT] EXISTS predicate. There may
be other ways of achieving your goal, too.


Thanks guys!  In fact I did see the difference between no row and a null
value within a row.  But it seemed there must be a way that I was missing.

It does look though that plpg is the way to go, otherwise it just seems
to obfuscate the code, or have other possible consequences.

-ds

--
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 tell if server is in backup mode?

[Toby Corkindale]

On 29/03/12 19:50, Gabriele Bartolini wrote:

Hi Toby,

Il 29/03/12 10:46, Gabriele Bartolini ha scritto:

Currently, there's no such a function. In general we simply check if a
'backup_label' file exists in PGDATA. However, it could be a good idea
to add it as admin function (I will have a think about it and possibly
come up with a patch).

My bad (thanks Carlo Ascani for pointing this out to me). Gilles Darold
just proposed such a patch and I looked into the commitfest website:
https://commitfest.postgresql.org/action/patch_view?id=803

I just added myself as a reviewer for PostgreSQL 9.3.


Cool, good to know it's on its way :)

Thanks Gabriele,

Toby

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

[GENERAL] octet_length operator: what encoding?

[Chris Angelico]

We have a number of varchar fields and I'm looking to see what the
greatest data length in any is, after UTF-8 encoding. The two-argument
length function appears (I think) to take a byte array, so it's the
opposite of what I'm looking for (give it a UTF-8 encoded string and
the second parameter 'UTF-8' and it'll count characters). The
octet_length function, though, doesn't accept an encoding argument.
What does it use?

ChrisA

-- 
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] default value returned from sql stmt

[Ken Tanzer]

It depends on what exactly it is you're trying to do, and where your
default is supposed to be used.  Are you wanting a single number returned?
 in that case something like this

SELECT COALESCE((SELECT anum FROM t1 WHERE anum=4 [ LIMIT 1 ]),100)

that would get you back a 4 or 100 in this case.  If your anums are not
unique, you'd want the "LIMIT 1" included.

Ken



On Thu, Mar 29, 2012 at 3:56 PM, David Salisbury wrote:

>
>
> On 3/29/12 4:26 PM, Chris Angelico wrote:
>
>> On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury
>>  wrote:
>>
>>> development=# select  coalesce(anum,100) from t1 where anum = 4;
>>>
>>
>> What you have there is rather different from COALESCE, as you're
>> looking for a case where the row completely doesn't exist. But you can
>> fudge it with an outer join.
>>
>> Untested code:
>>
>> WITH rowid AS (select 4 as anum) SELECT coalesce(anum,100) FROM rowid
>> LEFT JOIN t1 ON rowid.anum=t1.anum
>>
>> However, you may simply want a WHERE [NOT] EXISTS predicate. There may
>> be other ways of achieving your goal, too.
>>
>
> Thanks guys!  In fact I did see the difference between no row and a null
> value within a row.  But it seemed there must be a way that I was missing.
>
> It does look though that plpg is the way to go, otherwise it just seems
> to obfuscate the code, or have other possible consequences.
>
> -ds
>
>
> --
> 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] Move Tables From One Database to Another

[Bret Stern]

On Thu, 2012-03-29 at 14:49 -0500, Andy Colson wrote:
> On 3/29/2012 2:10 PM, Rich Shepard wrote:
> > I'm storing vector map attribute data in postgres tables and somehow
> > managed to create two databases (of similar names) rather than one. I want
> > to combine the two.
> >
> > For tables that exist in the one database I want to eliminate, I thought
> > to use pg_dump to create .sql files, then use pg_restore to add the
> > table to
> > the other database. Did this for one table (with 4201 rows), but
> > 'pg_restore
> > -d database_name -t table_name' appears to not complete; it seems to have
> > hung up somewhere. While I see nothing specific in the output file or the
> > pg_restore man page this must not be the proper approach.
> >
> > Also, I need suggestions on how to combine tables that exist in both
> > databases by adding rows from the source database not in the target
> > database
> > and modifying rows that differ.
> >
> > As I'm not a professional or full-time DBA I'm probably missing really
> > simple syntax and approaches. Your advice will be appreciated.
> >
> > Rich
> >
> >
> 
> 
> How many tables are we talking about.  If its a few tables, I'd rename them:
> 
> alter table lake rename to lake_old;
> ... etc
> 
> then dump it out and restore into the proper db.
> 
> The proper db will now have to tables, lake and lake_old, which you can 
> selective update some rows:
> 
> update lake
>set foo = (select foo from lake_old where lake_old.id = lake.id)
>where exists (select foo from lake_old where lake_old.id = lake.id);
> 
> !! The were exists is very important !!
> 
> and insert missing:
> 
> insert into lake
> select * from lake_old
> where not exists (select id from lake_old where lake_old.id = lake.id);
> 
>  > to use pg_dump to create .sql files, then use pg_restore to add the
>  > table to 
>  > it seems to have
>  > hung up somewhere.
> 
> 
> I wonder if a table was in use and pg_restore blocked on the drop table? 
>   If you don't mind replacing the entire table, this method should work. 
>   But if you want to merge the two tables, I would not go this route.
> 
> if you try the restore again, you can do:
> 
> ps ax|grep postg
> and see what statement its running.  You can also do:
> 
> select * from pg_locks where not granted;
> 
> and see if anything is blocked.
> 
> -Andy
> 

Good info. I think i'll plagiarize this thinking if you don't mind.
Thanks for the broad explanation.



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

Re: [GENERAL] could not read block... how could I identify/fix

[Naoko Reeves]

Vick,
Thank you very much. Yes, I just go ahead did what you said and all appears
to be fine.


On Thu, Mar 29, 2012 at 7:08 AM, Vick Khera  wrote:

> On Wed, Mar 28, 2012 at 6:31 PM, Naoko Reeves 
> wrote:
> > Do you think this should be the next step I might take?
> > Could you give me an advice of how I could identify corrupted error.
>
> It seems to me that since you can successfully dump the table (I
> assume you validated the data was all there somehow), you should go
> ahead and dump your whole DB, delete the current one, create it again,
> then restore it from scratch.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Naoko Reeves
http://www.anypossibility.com/

[GENERAL] Re: system catalog privilege and create privilege ??? how to control them?? thanks

[leaf_yxj]

Thank you very much!!! I really appreicate it.
 
Grace
At 2012-03-29 16:18:23,"Albe Laurenz *EXTERN* [via PostgreSQL]" 
 wrote:
leaf_yxj wrote:
> For oracle, the normal user can't see all the system catalog. but for
> postgresql, it looks like all the user can see the system catalog.
Should
> we limit the user read privilege to system catalog?

You can try that, but things may break in unexpected ways.
For example, psql's utility commands will probably stop working.
I would test any such change thoroughly.

Not all system catalogs are visible for everybody, tables and views
containing passwords for example can only be read by superusers.

PostgreSQL has fewer restrictions on reading system catalogs than
Oracle.
I can see how a seasoned Oracle DBA might feel uneasy if everybody
can find out all user names on the database cluster.

> In oracle, the system privilege has create table, create view,create
> function.  For postgresql database, how to control the user who only
can
> create table but can't create view. Based on the test I did, once the
user
> has the create privilege on the schema, the user will have any create
> privilege on that schema. In postgresql, Rule is used to control that
???
> very confused!

PostgreSQL's permission system is different from Oracle's.
Oracle has a lot of "system privileges" which PostgreSQL does not
have or need.

In Oracle, every user automatically has a schema of the same name
and there are no permissions on schema basis.  So you need system
privileges if you want to keep users from creating objects.

In PostgreSQL you can use schema permissions.
True, as soon as you have CREATE on a schema, you can create any
kind of object there.  That is, any kind of object that does not
depend on anything else.
To create a function, you need the USAGE privilege on the
procedural function.  You can revoke this right from PUBLIC and
only give it to the users you want.
To create a trigger, you need the TRIGGER privilege on the
table involved and the EXECUTE privilege on the trigger function.

So you see, most of what Oracle handles with system privileges is
handled with object privileges in PostgreSQL.  And you usually
can assign permissions in a finer granularity that way.

Of course it is confusing at first, but once you understand
PostgreSQL's permission system, there are few meaningful things
that you cannot achieve with it.
What's the use case for granting somebody CREATE TABLE, but
not CREATE VIEW?

Yours,
Laurenz Albe

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



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5602979.html
To unsubscribe from system catalog privilege and create privilege ??? how to 
control them?? thanks, click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/system-catalog-privilege-and-create-privilege-how-to-control-them-thanks-tp5601150p5605499.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] could not read block... how could I identify/fix

[Scott Marlowe]

On Thu, Mar 29, 2012 at 7:47 PM, Naoko Reeves  wrote:
> Vick,
> Thank you very much. Yes, I just go ahead did what you said and all appears
> to be fine.

You definitely need to check your hardware for faults, especially the
one that caused your server to crash.  Run some memory tests, drive
tests etc.

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

[GENERAL] double check the role has what's kind of the privilege? And the same for the objects. Thanks.

[leaf_yxj]

Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
THank you billions.  Another help. THanks.

After I create table and roles. I want to double check I grant the proper
privileges to the users. I can 
use \dp and \z command to check the objects privilege.  I can user pg_authid
to check the system privilege grantee to the users. Am I right??? the
below is what I get from my test database.



Q : what's the  differences between \dp and \z.

Thanks.
Regards.

Grace



rrp=# \dp
 Access privileges for database "rrp"
 Schema | Name |   Type   | Access privileges 
+--+--+---
 rrp| c| sequence | 
 rrp| p| sequence | 
 rrp| se1  | sequence | 
 rrp| t1   | table|
{grace=arwdxt/grace,user1=ar/grace,user2=r/grace}
 rrp| t2   | table| {grace=arwdxt/grace,user1=r/grace}

rrp=# \z
 Access privileges for database "rrp"
 Schema | Name |   Type   | Access privileges 
+--+--+---
 rrp| c| sequence | 
 rrp| p| sequence | 
 rrp| se1  | sequence | 
 rrp| t1   | table|
{grace=arwdxt/grace,user1=ar/grace,user2=r/grace}
 rrp| t2   | table| {grace=arwdxt/grace,user1=r/grace}


(5 rows)rrp=# select
rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin from
pg_authid;
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin 
-+--++---+-+-
 gpadmin | t| t  | t | t   | t
 grace   | f| t  | f | t   | t
 user1   | f| t  | f | f   | t
 user2   | f| t  | f | f   | t
 user4   | f| t  | f | f   | t
(5 rows)





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605564.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] double check the role has what's kind of the privilege? And the same for the objects. Thanks.

[Chris Travers]

On Thu, Mar 29, 2012 at 7:49 PM, leaf_yxj  wrote:
> Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
> THank you billions.  Another help. THanks.
>
> After I create table and roles. I want to double check I grant the proper
> privileges to the users. I can
> use \dp and \z command to check the objects privilege.  I can user pg_authid
> to check the system privilege grantee to the users. Am I right??? the
> below is what I get from my test database.

Cna you be specific about what you are trying to check.  Are you
trying to check whether a user has access to a given role?  If so
check out pg_has_role().

Best Wishes,
Chris Travers

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

[GENERAL] Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

[leaf_yxj]

Hi Chris,
 
My bosses ask me  to list all the users and all the privilege which the 
superuser granted  to the users.
Then they can double check that I did right thing or not?
 
Thanks.
 
Grace
At 2012-03-30 10:54:50,"Chris Travers-5 [via PostgreSQL]" 
 wrote:
On Thu, Mar 29, 2012 at 7:49 PM, leaf_yxj <[hidden email]> wrote:
> Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
> THank you billions.  Another help. THanks.
>
> After I create table and roles. I want to double check I grant the proper
> privileges to the users. I can
> use \dp and \z command to check the objects privilege.  I can user pg_authid
> to check the system privilege grantee to the users. Am I right??? the
> below is what I get from my test database.

Cna you be specific about what you are trying to check.  Are you
trying to check whether a user has access to a given role?  If so
check out pg_has_role().

Best Wishes,
Chris Travers

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



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605567.html
To unsubscribe from double check the role has what's kind of the privilege? And 
the same for the objects. Thanks., click here.
NAML
Hi

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605597.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.