subject:"Re\: \[GENERAL\] Schema"

Re: [GENERAL] Schema/table replication

2017-09-06 Thread Nico Williams

On Wed, Sep 06, 2017 at 08:22:14AM -0700, Steve Atkins wrote:
> > On Sep 6, 2017, at 6:00 AM, Marcin Giedz  wrote:
> > 
> > Hi, is there any way (3rd party software) to replicate particular 
> > schema/table not the whole database with streaming replication built-in 
> > mechanism ?
> 
> I don't believe so. You can do that with logical replication in v10 -
> https://www.postgresql.org/docs/10/static/logical-replication.html.

Well, session_replication_role exists so that you can implement your own
logical replication, and with that you can have full control over what
to replicate and what not replicate.  There is no builtin functionality
that does this.  Third-party software?  Try this search and similar in
other engines:

https://github.com/search?utf8=%E2%9C%93&q=session_replication_role+extension%3Asql&type=Code&ref=advsearch&l=&l=

> pglogical will give you much the same functionality on current
> releases. https://www.2ndquadrant.com/en/resources/pglogical/ -
> installation isn't too painful (though the docs are a little sparse
> when it comes to which node you should run which command on. Make the
> postgres.conf changes on master and slave nodes, as slave nodes need
> replication slots too(?)).
> 
> There are a bunch of trigger-based replication frameworks that'll work
> too, though less efficiently - Slony is widely used, and I used
> Bucardo successfully for years before moving to pglogical.

Yeah.


-- 
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] Schema/table replication

2017-09-06 Thread Steve Atkins


> On Sep 6, 2017, at 8:48 AM, Marcin Giedz  wrote:
> 
> Does pglogical support views replication as I can't find it in any 
> restrictions ?

There's no need to replicate the contents of a view, as it doesn't contain any 
data.

pglogical can replicate the initial schema, including any views, but won't 
replicate DDL changes automatically after that. It does provide a clean way to 
replicate DDL from the master to slaves with pglogical.replicate_ddl_command().

Cheers,
  Steve

> > On Sep 6, 2017, at 6:00 AM, Marcin Giedz  wrote:
> > 
> > Hi, is there any way (3rd party software) to replicate particular 
> > schema/table not the whole database with streaming replication built-in 
> > mechanism ?
> 
> I don't believe so. You can do that with logical replication in v10 - 
> https://www.postgresql.org/docs/10/static/logical-replication.html.
> 
> pglogical will give you much the same functionality on current releases. 
> https://www.2ndquadrant.com/en/resources/pglogical/ - installation isn't too 
> painful (though the docs are a little sparse when it comes to which node you 
> should run which command on. Make the postgres.conf changes on master and 
> slave nodes, as slave nodes need replication slots too(?)).
> 
> There are a bunch of trigger-based replication frameworks that'll work too, 
> though less efficiently - Slony is widely used, and I used Bucardo 
> successfully for years before moving to pglogical.
> 
> Cheers,
>   Steve
> 
> 



-- 
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] Schema/table replication

2017-09-06 Thread Marcin Giedz

Does pglogical support views replication as I can't find it in any restrictions 
? 

Thx 
Marcin 

Od: "Steve Atkins"  
Do: pgsql-general@postgresql.org 
Wysłane: środa, 6 wrzesień 2017 17:22:14 
Temat: Re: [GENERAL] Schema/table replication 

> On Sep 6, 2017, at 6:00 AM, Marcin Giedz  wrote: 
> 
> Hi, is there any way (3rd party software) to replicate particular 
> schema/table not the whole database with streaming replication built-in 
> mechanism ? 

I don't believe so. You can do that with logical replication in v10 - 
https://www.postgresql.org/docs/10/static/logical-replication.html. 

pglogical will give you much the same functionality on current releases. 
https://www.2ndquadrant.com/en/resources/pglogical/ - installation isn't too 
painful (though the docs are a little sparse when it comes to which node you 
should run which command on. Make the postgres.conf changes on master and slave 
nodes, as slave nodes need replication slots too(?)). 

There are a bunch of trigger-based replication frameworks that'll work too, 
though less efficiently - Slony is widely used, and I used Bucardo successfully 
for years before moving to pglogical. 

Cheers, 
Steve

Re: [GENERAL] Schema/table replication

2017-09-06 Thread Steve Atkins

> On Sep 6, 2017, at 6:00 AM, Marcin Giedz  wrote:
> 
> Hi, is there any way (3rd party software) to replicate particular 
> schema/table not the whole database with streaming replication built-in 
> mechanism ?

I don't believe so. You can do that with logical replication in v10 - 
https://www.postgresql.org/docs/10/static/logical-replication.html.

pglogical will give you much the same functionality on current releases. 
https://www.2ndquadrant.com/en/resources/pglogical/ - installation isn't too 
painful (though the docs are a little sparse when it comes to which node you 
should run which command on. Make the postgres.conf changes on master and slave 
nodes, as slave nodes need replication slots too(?)).

There are a bunch of trigger-based replication frameworks that'll work too, 
though less efficiently - Slony is widely used, and I used Bucardo successfully 
for years before moving to pglogical.

Cheers,
  Steve

-- 
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] schema advice for event stream with tagging and filtering

2016-12-12 Thread Chris Withers


On 16/08/2016 15:10, Ilya Kazakevich wrote:

An event is a row with a primary key along the lines of (colo, host,
category) and an associated set of tags, where each tag has a type
and a value
(eg: {"color": "red", "owner": "fred", "status": "open"...}).


What about  simple table with several columns and hstore  field for tags?


BTW, "pure SQL" approach here is to use separate tables: Tags(TagId, TagName) 
and TagValues(EventId,TagId,Value).


Well, maybe, but none of us wants to do that ;-)


But in this case it will be painful to filter events by tag values directly, so only 
separate denormalized OLAP table should be used in "pure SQL":)


I don't understand the second half of this I'm afraid...


PostgreSQL, however, supports key-value based hstore.


Right, but hstore only allows single values for each key, if I 
understand correctly?


Okay, so that leaves me with a jsonb "tags" column with a gin index, but 
I still have a couple of choices..


So, in order to best answer these types of queries:


- show me a list of tag types and the count of the number of events of that
type

- show me all events that have tag1=x, tag2=y and does not have tag3


...which of the following is going to be most performant:

# SELECT '{"tag1":["v1", "v2", "v3"]}'::jsonb @> '{"tag1": ["v1"]}'::jsonb;
 ?column?
--
 t
(1 row)

# SELECT '[{"tag1":"v1"}, {"tag1": "v2"}, {"tag1": "v3"}]'::jsonb @> 
'[{"tag1": "v1"}]'::jsonb;

 ?column?
--
 t
(1 row)

So, should I go for a tag name that maps to a list of values for that 
tag, or should I go for a sequence of one-entry mappings of tag name to 
tag value?


cheers,

Chris


--
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] schema advice for event stream with tagging and filtering

2016-08-16 Thread Venkata B Nagothi

On Tue, Aug 16, 2016 at 6:38 PM, Chris Withers 
wrote:

> Hi All,
>
> What would be the best schema to use when looking to implement an event
> stream with tagging and filtering?
>
> An event is a row with a primary key along the lines of (colo, host,
> category) and an associated set of tags, where each tag has a type and a
> value (eg: {"color": "red", "owner": "fred", "status": "open"...}).
>
> Events come in as a streams of creates/updates as a cluster of http posts
> to a web app.
>

Not sure which version of PostgreSQL you are using. Did you consider
JSON/JSONB data-types which is strongly supported by PostgreSQL ? You can
consider having a table with JSON or JSONB column type. If you can use
JSONB, then, it supports indexing as well.

That should make web app easy to push JSON format data to PostgreSQL.

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] schema advice for event stream with tagging and filtering

2016-08-16 Thread Ilya Kazakevich

>>> An event is a row with a primary key along the lines of (colo, host,
>>> category) and an associated set of tags, where each tag has a type
>>> and a value
>>> (eg: {"color": "red", "owner": "fred", "status": "open"...}).
>>
>> What about  simple table with several columns and hstore  field for tags?

BTW, "pure SQL" approach here is to use separate tables: Tags(TagId, TagName) 
and TagValues(EventId,TagId,Value). 
But in this case it will be painful to filter events by tag values directly, so 
only separate denormalized OLAP table should be used in "pure SQL":)
PostgreSQL, however, supports key-value based hstore.

>> You may also normalize it (move hosts and categories to separate table).
>
>Why? These form part of the primary key for the event...

Host and category could be part of PK, but it may be better to have "HostId" 
field and "Hosts(HostId, Host)" table than "text" field with many "www.foo.bar" 
values) 

>What kind of index is recommended here? The kind of queries would be:
>
>- show me a list of tag types and the count of the number of events of that
>type
>
>- show me all events that have tag1=x, tag2=y and does not have tag3

Hstore supports GIN and GIST 
(https://www.postgresql.org/docs/9.1/static/textsearch-indexes.html). I'd start 
with  GIN.

Do you need real-time data or, say, one-day old data is ok? In latter case it 
is better to load data to denormalized table to speed-up queries and use no 
index on normalized(OLTP) table because index update operation is not free)


Ilya Kazakevich

JetBrains
http://www.jetbrains.com
The Drive to Develop



-- 
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] schema advice for event stream with tagging and filtering

2016-08-16 Thread Chris Withers


On 16/08/2016 14:29, Ilya Kazakevich wrote:

Hi,


An event is a row with a primary key along the lines of (colo, host,
category) and an associated set of tags, where each tag has a type and a value
(eg: {"color": "red", "owner": "fred", "status": "open"...}).


What about  simple table with several columns and hstore  field for tags?


Interesting!


You may also normalize it (move hosts and categories to separate table).


Why? These form part of the primary key for the event...


indexes should help you with fast filtering, or you may load data from this 
table to denormalized olap table once a day and build index there to speed-up 
queries.


What kind of index is recommended here? The kind of queries would be:

- show me a list of tag types and the count of the number of events of 
that type


- show me all events that have tag1=x, tag2=y and does not have tag3

cheers,

Chris


--
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] schema advice for event stream with tagging and filtering

2016-08-16 Thread Ilya Kazakevich

Hi,

>An event is a row with a primary key along the lines of (colo, host,
>category) and an associated set of tags, where each tag has a type and a value
>(eg: {"color": "red", "owner": "fred", "status": "open"...}).

What about  simple table with several columns and hstore  field for tags?
You may also normalize it (move hosts and categories to separate table).

indexes should help you with fast filtering, or you may load data from this 
table to denormalized olap table once a day and build index there to speed-up 
queries.

Ilya Kazakevich

JetBrains
http://www.jetbrains.com
The Drive to Develop




-- 
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] Schema Size - PostgreSQL 9.2

2016-03-19 Thread Melvin Davidson

On Thu, Mar 17, 2016 at 4:45 PM, Karsten Hilbert 
wrote:

> On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote:
>
> > Can you please provide me a Query that tells me how much space is a
> Schema
> > in my DB?
>
> There's been a discussion on that recently (like last month)
> which can be found in the archive.
>
> 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
>


This should give you what you need:

SELECT n.nspname as schema,
   pg_size_pretty(sum(pg_total_relation_size(quote_ident(n.nspname)||
'.' || quote_ident(c.relname as size,
   sum(pg_total_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(c.relname))) as size_bytes
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
GROUP BY 1
ORDER BY 3 DESC, 1, 2;


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

Re: [GENERAL] Schema Size - PostgreSQL 9.2

2016-03-19 Thread Karsten Hilbert

On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote:

> Can you please provide me a Query that tells me how much space is a Schema
> in my DB?

There's been a discussion on that recently (like last month)
which can be found in the archive.

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] Schema Size

2016-03-01 Thread drum.lu...@gmail.com

On 2 March 2016 at 12:23, Scott Mead  wrote:

>
> On Tue, Mar 1, 2016 at 6:07 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> You should read the definitions for the functions you are using to
>> retrieve the sizes.
>>
>> http://www.postgresql.org/docs/current/static/functions-admin.html
>>
>> +1, you've gotta be careful with each of these, they all tend to hide
> different, yet critical components of size that you may be having trouble
> resolving.
>
>  The other thing to consider is that this isn't including any on-disk
> space required for your change traffic in the WAL.  Your $PGDATA will
> always be larger than the sum of all your databases sizes...
>
>
>> On Tue, Mar 1, 2016 at 3:48 PM, drum.lu...@gmail.com <
>> drum.lu...@gmail.com> wrote:
>>
>>> Hi there
>>>
>>> Wanna see how size a schema is in my PostgreSQL 9.2
>>>
>>> Got two queries - they return different values... can u please check?
>>>
>>> cheers;
>>>
>>> Query 1:
>>> SELECT schema_name,
>>> pg_size_pretty(sum(table_size)::bigint) as "disk space",
>>> (sum(table_size) / pg_database_size(current_database())) * 100
>>> as "percent"
>>> FROM (
>>>  SELECT pg_catalog.pg_namespace.nspname as schema_name,
>>>  pg_relation_size(pg_catalog.pg_class.oid) as table_size
>>>  FROM   pg_catalog.pg_class
>>>  JOIN pg_catalog.pg_namespace
>>>  ON relnamespace = pg_catalog.pg_namespace.oid
>>> ) t
>>> GROUP BY schema_name
>>> ORDER BY schema_name
>>>
>>>
>> pg_relation_size: "Disk space used by the specified fork ('main',
>> 'fsm', 'vm', or 'init') of the specified table or index"
>>
>> The 'init' fork is (I think) non-zero but extremely small.
>> TOAST for a given relation is considered its own table
>>
>>
>>> Query 2:
>>> select schemaname,
>>> pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s
>>> from pg_stat_user_tables
>>> group by schemaname
>>>
>>
>> pg_table_size: "Disk space used by the specified table, excluding indexes
>> (but including TOAST, free space map, and visibility map)"
>>
>
> Personally, I'm a huge fan of 'pg_total_relation_size' which is all of
> pg_table_size + indexes.  It really depends on specifically what you're
> trying to count.  If you're looking for the total disk space required by
> your tables in a schema, I always [personally] want to include indexes in
> this count to make sure I understand the total impact on disk of accessing
> my relations.
>
>
>>
>> David J.
>>
>>
>

So.. I'm doing this way:

CREATE OR REPLACE FUNCTION pg_schema_size(text) RETURNS BIGINT AS
$$SELECT SUM(pg_total_relation_size(quote_ident(schemaname) || '.' ||
quote_ident(tablename)))::BIGINT FROM pg_tables WHERE schemaname = $1

$$ LANGUAGE SQL;

Link: https://wiki.postgresql.org/wiki/Schema_Size


That's working - But I'd like to test it.. to compare the results with
another one trustfull - Do you have some?

Re: [GENERAL] Schema Size

2016-03-01 Thread Scott Mead

On Tue, Mar 1, 2016 at 6:07 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> You should read the definitions for the functions you are using to
> retrieve the sizes.
>
> http://www.postgresql.org/docs/current/static/functions-admin.html
>
> +1, you've gotta be careful with each of these, they all tend to hide
different, yet critical components of size that you may be having trouble
resolving.

 The other thing to consider is that this isn't including any on-disk space
required for your change traffic in the WAL.  Your $PGDATA will always be
larger than the sum of all your databases sizes...


> On Tue, Mar 1, 2016 at 3:48 PM, drum.lu...@gmail.com  > wrote:
>
>> Hi there
>>
>> Wanna see how size a schema is in my PostgreSQL 9.2
>>
>> Got two queries - they return different values... can u please check?
>>
>> cheers;
>>
>> Query 1:
>> SELECT schema_name,
>> pg_size_pretty(sum(table_size)::bigint) as "disk space",
>> (sum(table_size) / pg_database_size(current_database())) * 100
>> as "percent"
>> FROM (
>>  SELECT pg_catalog.pg_namespace.nspname as schema_name,
>>  pg_relation_size(pg_catalog.pg_class.oid) as table_size
>>  FROM   pg_catalog.pg_class
>>  JOIN pg_catalog.pg_namespace
>>  ON relnamespace = pg_catalog.pg_namespace.oid
>> ) t
>> GROUP BY schema_name
>> ORDER BY schema_name
>>
>>
> pg_relation_size: "Disk space used by the specified fork ('main', 'fsm',
> 'vm', or 'init') of the specified table or index"
>
> The 'init' fork is (I think) non-zero but extremely small.
> TOAST for a given relation is considered its own table
>
>
>> Query 2:
>> select schemaname,
>> pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s
>> from pg_stat_user_tables
>> group by schemaname
>>
>
> pg_table_size: "Disk space used by the specified table, excluding indexes
> (but including TOAST, free space map, and visibility map)"
>

Personally, I'm a huge fan of 'pg_total_relation_size' which is all of
pg_table_size + indexes.  It really depends on specifically what you're
trying to count.  If you're looking for the total disk space required by
your tables in a schema, I always [personally] want to include indexes in
this count to make sure I understand the total impact on disk of accessing
my relations.


>
> David J.
>
>

Re: [GENERAL] Schema Size

2016-03-01 Thread David G. Johnston

You should read the definitions for the functions you are using to retrieve
the sizes.

http://www.postgresql.org/docs/current/static/functions-admin.html

On Tue, Mar 1, 2016 at 3:48 PM, drum.lu...@gmail.com 
wrote:

> Hi there
>
> Wanna see how size a schema is in my PostgreSQL 9.2
>
> Got two queries - they return different values... can u please check?
>
> cheers;
>
> Query 1:
> SELECT schema_name,
> pg_size_pretty(sum(table_size)::bigint) as "disk space",
> (sum(table_size) / pg_database_size(current_database())) * 100
> as "percent"
> FROM (
>  SELECT pg_catalog.pg_namespace.nspname as schema_name,
>  pg_relation_size(pg_catalog.pg_class.oid) as table_size
>  FROM   pg_catalog.pg_class
>  JOIN pg_catalog.pg_namespace
>  ON relnamespace = pg_catalog.pg_namespace.oid
> ) t
> GROUP BY schema_name
> ORDER BY schema_name
>
>
pg_relation_size: "Disk space used by the specified fork ('main', 'fsm',
'vm', or 'init') of the specified table or index"

The 'init' fork is (I think) non-zero but extremely small.
TOAST for a given relation is considered its own table


> Query 2:
> select schemaname,
> pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s
> from pg_stat_user_tables
> group by schemaname
>

pg_table_size: "Disk space used by the specified table, excluding indexes
(but including TOAST, free space map, and visibility map)"

David J.

Re: [GENERAL] Schema Help Needed To Get Unstuck

2015-07-23 Thread Rich Shepard


On Thu, 23 Jul 2015, Melvin Davidson wrote:


Does this help?


Melvin,

  Yep.

  After pondering David's response I recognized my error: I had the permit
table as the center of the relationships when it should be the conditions
table. As you wrote, permits, parameters, and locations all feed the
conditions table (where additional attributes are added), then a row from
that table is added to the monitoring table where quantities and other
attributes are entered.

  Sometimes we get so deep in a rut we can't see over the edge and find the
correct path.

Thanks very much,

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] Schema Help Needed To Get Unstuck

2015-07-23 Thread Melvin Davidson

Does this help?

CREATE TABLE permits
(
permit_number bigint,
...,
other_columns ,
...,
CONSTRAINT permits_pk PRIMARY KEY (permit_number)
);

CREATE TABLE parameters
(
permit_number bigint,
parameter varchar(50),
...,
other_columns ,
...,
CONSTRAINT parameters_pk PRIMARY KEY (permit_number, parameter),
CONSTRAINT parameters_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number)
);

CREATE TABLE locations
(
permit_number bigint,
location varchar(50),
...,
other_columns ,
...,
CONSTRAINT locations_pk PRIMARY KEY (permit_number, location),
CONSTRAINT locations_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number)
);

CREATE TABLE conditions
(
permit_number bigint,
condition varchar(50),
location varchar(50),
frequency varchar(10),
start_time timestamp,
end_time timestamp,
...,
other_columns ,
...,
CONSTRAINT locations_pk PRIMARY KEY (permit_number, condition, location)
CONSTRAINT conditions_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number),
CONSTRAINT conditions_location_fk FOREIGN KEY (permit_number, location)
  REFERENCES locations (permit_number, location)
);

CREATE TABLE monitoring
(
permit_number bigint,
monitor_date timestamp,
location varchar(50),
frequency varchar(10),
start_time timestamp,
end_time timestamp,
...,
other_columns ,
...,
CONSTRAINT locations_pk PRIMARY KEY (permit_number, condition, location)
CONSTRAINT monitoring_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number),
CONSTRAINT monitoring_location_fk FOREIGN KEY (permit_number, location)
  REFERENCES locations (permit_number, location),
CONSTRAINT monitoring_parameter_fk FOREIGN KEY (permit_number, parameter)
  REFERENCES locations (permit_number, parameter)

);

Re: [GENERAL] Schema Help Needed To Get Unstuck

2015-07-23 Thread Rich Shepard


On Thu, 23 Jul 2015, David G. Johnston wrote:


Conditions <- (Permit + Location + Parameter + Frequency + Temporal (from,
until))
Monitoring <- (Condition + Event)

While you may require additional modelling tables to support your user
interface (i.e., picking valid combinations of PeLoPa when creating a new
condition) the fundamental concept is that each permit+location+parameter
combination needs to be monitored during a given period (temporal) at a
given frequency in that period.  Every time that combination generates a
number you combine the "Condition" with the event (data + time) and add the
result to the monitoring table.

Active combinations of permits, locations, and parameters can be directly
derived from the conditions table.  The temporal fields facilitate history
generation.


David,

  This is what I was working to achieve, but did not have the details
straight. I'll ponder this and make sure I set the proper FKs on the
appropriate tables.

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] Schema Help Needed To Get Unstuck

2015-07-23 Thread David G. Johnston

On Thu, Jul 23, 2015 at 3:06 PM, Rich Shepard 
wrote:

>   While designing the schema for a new application have become
> high-centered
> and stuck relating some many-to-many tables. Fresh eyes and suggestions are
> needed on how to create many-to-many association tables among these five.
>
>   Table 'Permits': Contains information about each permit, PK is permit
> number.
>
>   Table 'Parameters': Contains all parameters (physical, chemical,
> biological, operational) by name and other attributes. This is a record of
> all parameters over the life of the permit. (Many parameters, many
> permits.)
>
>   Table 'Locations': Contains details about each monitoring location. This
> is a record of all locations over the life of the permit. (Many locations,
> many permits.)
>
>   Table 'Conditions': Contains permit numbers and which paramenters are to
> be monitored at which locations and at what frequencies and over what time
> period. I'm thinking one row per permit that reference the permit number,
> parameters, locations, frequencies, and active dates. Each location has
> multiple parameters, and each parameter is monitored at multiple locations.
> FK references permit number. (Many parameters, many locations, many
> frequencies for each of many permits.)
>
>   Table 'Monitoring': Contains columns for date, location, parameter,
> quantity, and other attributes. FK references permit number. (Many rows for
> each permit.)
>
>
Conditions <- (Permit + Location + Parameter + Frequency + Temporal (from,
until))
Monitoring <- (Condition + Event)

While you may require additional modelling tables to support your user
interface (i.e., picking valid combinations of PeLoPa when creating a new
condition) the fundamental concept is that each permit+location+parameter
combination needs to be monitored during a given period (temporal) at a
given frequency in that period.  Every time that combination generates a
number you combine the "Condition" with the event (data + time) and add the
result to the monitoring table.

Active combinations of permits, locations, and parameters can be directly
derived from the conditions table.  The temporal fields facilitate history
generation.

David J.

Re: [GENERAL] schema or database

2015-04-13 Thread Jim Nasby

On 4/13/15 6:21 AM, Anil Menon wrote:

In addition to all these comments

- If you use multiple databases, if you want to keep some "common"
tables (example counties_Table, My_company_details), its going to be a pain
- if you want to access tables across databases - you might need to
start using FDWs (which is going to be a administrative pain - syncing
passwords and stuff)
- you could set up security easier with multiple schemas - example userA
can only use schema A and no access to other schemas

Please don't top-post.

On Mon, Apr 13, 2015 at 4:48 PM, Pavel Stehule mailto:pavel.steh...@gmail.com>> wrote:

2015-04-13 10:43 GMT+02:00 Albe Laurenz mailto:laurenz.a...@wien.gv.at>>:

Michael Cheung wrote:
> I have many similar database to store data for every customer.
> Structure of database is almost the same.
> As I use same application to control all these data, so I can only use
> one database user to connect to these database.
> And I have no needs to query table for different customer together.
>
> I wonder which I should use, different shema or different database to 
store data?
>
> I 'd like to know the advantage and disadvantage for using schema or 
database.

In addition to what others have said:

If you use multiple schemas within one database, the danger is
greater that
data are written to or read from the wrong schema if your
application has a bug
ans does not make sure to always set search_path or qualify
every access with a
schema name.

With multiple databases you are guaranteed not to access data
from a different
database.

The main downside that I see to multiple databases is the
overhead: each of
the databases will have its own pg_catalog tables.

It can be advantage - if your schema is pretty complex - thousands
procedures, tables, then separate pg_catalog can be better - there
are issues with pg_dump, pg_restore.

So it depends on catalog size and complexity.

Two things no one has mentioned. First, you could also use row-level 
security. If you plan on each customer having a fairly small amount of 
data, this is by far your most efficient option. Anything else will 
result in either huge catalogs or a lot of wasted catalog space.

Second, if you do per-database, that makes it trivial to scale across 
multiple servers.

Regarding backups; you can easily do partial either way with pg_dump; 
there's really no difference. You can't do partial with PITR, but that's 
true for both schema and database.

--
Jim Nasby, Data Architect, Blue Treble Consulting
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] schema or database

2015-04-13 Thread Anil Menon

In addition to all these comments

- If you use multiple databases, if you want to keep some "common" tables
(example counties_Table, My_company_details), its going to be a pain
- if you want to access tables across databases - you might need to start
using FDWs (which is going to be a administrative pain - syncing passwords
and stuff)
- you could set up security easier with multiple schemas - example userA
can only use schema A and no access to other schemas

Regards
AK



On Mon, Apr 13, 2015 at 4:48 PM, Pavel Stehule 
wrote:

>
>
> 2015-04-13 10:43 GMT+02:00 Albe Laurenz :
>
>> Michael Cheung wrote:
>> > I have many similar database to store data for every customer.
>> > Structure of database is almost the same.
>> > As I use same application to control all these data, so I can only use
>> > one database user to connect to these database.
>> > And I have no needs to query table for different customer together.
>> >
>> > I wonder which I should use, different shema or different database to
>> store data?
>> >
>> > I 'd like to know the advantage and disadvantage for using schema or
>> database.
>>
>> In addition to what others have said:
>>
>> If you use multiple schemas within one database, the danger is greater
>> that
>> data are written to or read from the wrong schema if your application has
>> a bug
>> ans does not make sure to always set search_path or qualify every access
>> with a
>> schema name.
>>
>> With multiple databases you are guaranteed not to access data from a
>> different
>> database.
>>
>> The main downside that I see to multiple databases is the overhead: each
>> of
>> the databases will have its own pg_catalog tables.
>>
>
> It can be advantage - if your schema is pretty complex - thousands
> procedures, tables, then separate pg_catalog can be better - there are
> issues with pg_dump, pg_restore.
>
> So it depends on catalog size and complexity.
>
> Regards
>
> Pavel
>
>
>
>
>>
>> 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] schema or database

2015-04-13 Thread Pavel Stehule

2015-04-13 10:43 GMT+02:00 Albe Laurenz :

> Michael Cheung wrote:
> > I have many similar database to store data for every customer.
> > Structure of database is almost the same.
> > As I use same application to control all these data, so I can only use
> > one database user to connect to these database.
> > And I have no needs to query table for different customer together.
> >
> > I wonder which I should use, different shema or different database to
> store data?
> >
> > I 'd like to know the advantage and disadvantage for using schema or
> database.
>
> In addition to what others have said:
>
> If you use multiple schemas within one database, the danger is greater that
> data are written to or read from the wrong schema if your application has
> a bug
> ans does not make sure to always set search_path or qualify every access
> with a
> schema name.
>
> With multiple databases you are guaranteed not to access data from a
> different
> database.
>
> The main downside that I see to multiple databases is the overhead: each of
> the databases will have its own pg_catalog tables.
>

It can be advantage - if your schema is pretty complex - thousands
procedures, tables, then separate pg_catalog can be better - there are
issues with pg_dump, pg_restore.

So it depends on catalog size and complexity.

Regards

Pavel




>
> 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] schema or database

2015-04-13 Thread Albe Laurenz

Michael Cheung wrote:
> I have many similar database to store data for every customer.
> Structure of database is almost the same.
> As I use same application to control all these data, so I can only use
> one database user to connect to these database.
> And I have no needs to query table for different customer together.
> 
> I wonder which I should use, different shema or different database to store 
> data?
> 
> I 'd like to know the advantage and disadvantage for using schema or database.

In addition to what others have said:

If you use multiple schemas within one database, the danger is greater that
data are written to or read from the wrong schema if your application has a bug
ans does not make sure to always set search_path or qualify every access with a
schema name.

With multiple databases you are guaranteed not to access data from a different
database.

The main downside that I see to multiple databases is the overhead: each of
the databases will have its own pg_catalog tables.

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] schema or database

2015-04-13 Thread John R Pierce


On 4/13/2015 12:07 AM, Alban Hertroys wrote:

That's easier to backup, sure, but you can't restore a single customer's schema 
easily that way. So if one customer messes up their data big time, you'll need 
to restore a backup for all customers in the DB.


if you use pg_dump -Fc, then you can specify the schema at pg_restore time.



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

Re: [GENERAL] schema or database

2015-04-13 Thread Alban Hertroys


> On 13 Apr 2015, at 4:20, Ian Barwick  wrote:
> 
> On 13/04/15 11:08, Michael Cheung wrote:
>> hi, all;
>> 
>> I am new here. And I need some suggestion.
>> 
>> I have many similar database to store data for every customer.
>> Structure of database is almost the same.
>> As I use same application to control all these data, so I can only use
>> one database user to connect to these database.
>> And I have no needs to query table for different customer together.
>> 
>> I wonder which I should use, different shema or different database to store 
>> data?
>> 
>> I 'd like to know the advantage and disadvantage for using schema or 
>> database.
> 
> If as you say access to the database is via a single application database
> user, it will probably make more sense to use multiple schemas rather than
> multiple databases. Keeping everything in one database will simplify
> administration (e.g. making backups - ypu'll just need to dump the one 
> database
> rather than looping through a variable number) and will make life easier if 
> you
> ever need to do some kind of query involving multiple customers.

That's easier to backup, sure, but you can't restore a single customer's schema 
easily that way. So if one customer messes up their data big time, you'll need 
to restore a backup for all customers in the DB.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find 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] schema or database

2015-04-12 Thread Michael Cheung

Thanks for your additional comment.
It is more clear, I'd better to using schema more than using database.

yours, michael



On Sun, 12 Apr 2015 19:24:30 -0700
John R Pierce  wrote:

> On 4/12/2015 7:20 PM, Ian Barwick wrote:
> > If as you say access to the database is via a single application database
> > user, it will probably make more sense to use multiple schemas rather than
> > multiple databases. Keeping everything in one database will simplify
> > administration (e.g. making backups - ypu'll just need to dump the one 
> > database
> > rather than looping through a variable number) and will make life easier if 
> > you
> > ever need to do some kind of query involving multiple customers.
> > There will also be less overhead when adding a new schema vs adding
> > a new database.
> 
> and less overhead in connections, as one client connection can serve multiple 
> customers
> 
> -- 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



-- 
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] schema or database

2015-04-12 Thread Michael Cheung

Thanks for your suggestion.
I'd like to use schema as you suggest.

yours, michael

On Mon, 13 Apr 2015 11:20:59 +0900
Ian Barwick  wrote:

> On 13/04/15 11:08, Michael Cheung wrote:
> > hi, all;
> > 
> > I am new here. And I need some suggestion.
> > 
> > I have many similar database to store data for every customer.
> > Structure of database is almost the same.
> > As I use same application to control all these data, so I can only use
> > one database user to connect to these database.
> > And I have no needs to query table for different customer together.
> > 
> > I wonder which I should use, different shema or different database to store 
> > data?
> > 
> > I 'd like to know the advantage and disadvantage for using schema or 
> > database.
> 
> If as you say access to the database is via a single application database
> user, it will probably make more sense to use multiple schemas rather than
> multiple databases. Keeping everything in one database will simplify
> administration (e.g. making backups - ypu'll just need to dump the one 
> database
> rather than looping through a variable number) and will make life easier if 
> you
> ever need to do some kind of query involving multiple customers.
> There will also be less overhead when adding a new schema vs adding
> a new database.
> 
> 
> Regards
> 
> Ian Barwick
> 
> -- 
>  Ian Barwick   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
> 
> 
> -- 
> 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] schema or database

2015-04-12 Thread John R Pierce


On 4/12/2015 7:20 PM, Ian Barwick wrote:

If as you say access to the database is via a single application database
user, it will probably make more sense to use multiple schemas rather than
multiple databases. Keeping everything in one database will simplify
administration (e.g. making backups - ypu'll just need to dump the one database
rather than looping through a variable number) and will make life easier if you
ever need to do some kind of query involving multiple customers.
There will also be less overhead when adding a new schema vs adding
a new database.


and less overhead in connections, as one client connection can serve 
multiple customers


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

Re: [GENERAL] schema or database

2015-04-12 Thread Ian Barwick

On 13/04/15 11:08, Michael Cheung wrote:
> hi, all;
> 
> I am new here. And I need some suggestion.
> 
> I have many similar database to store data for every customer.
> Structure of database is almost the same.
> As I use same application to control all these data, so I can only use
> one database user to connect to these database.
> And I have no needs to query table for different customer together.
> 
> I wonder which I should use, different shema or different database to store 
> data?
> 
> I 'd like to know the advantage and disadvantage for using schema or database.

If as you say access to the database is via a single application database
user, it will probably make more sense to use multiple schemas rather than
multiple databases. Keeping everything in one database will simplify
administration (e.g. making backups - ypu'll just need to dump the one database
rather than looping through a variable number) and will make life easier if you
ever need to do some kind of query involving multiple customers.
There will also be less overhead when adding a new schema vs adding
a new database.

Regards

Ian Barwick

-- 
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
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] Schema Search Path Problem

2013-10-10 Thread Pavel Stehule

Hello


2013/10/10 Kalai R 

> Hi,
>
> I am developing .Net application using PSQL. I am using npgsql to connect
> PSQL database. In single database I have more than one schemas. After I
> connect database I set search path using "Set Searchpath command" for the
> required schema. It works perfectly.
>
> But after sometime, I need to change schema. so I set again search path
>   using "Set Searchpath command" . Now if I execute a single query the
> changed search path works correctly. But if I execute a pl/sql function
> written in background the changed search path does not work.
>


It is expected behave for older releases and it is fixed in 9.3. PL/pgSQL
procedures holds a execution plans in session plan cache and changing
search_path didn't invalidate caches. There are two workarounds: a) using
special instances of functions for schema, b) change search_path only once
after connect, and if you need a new search_path, then disconnect, and
create new connect.

Regards

Pavel Stehule


>
> Help me to solve this issue.
>
> Thanks.
>

Re: [GENERAL] Schema (Search path issue) on PostgreSQL9.2

2013-05-16 Thread Tom Lane

chiru r  writes:
> Is there any schema(set search_path) behaviour changes from  PostgreSQL9.1
> to PostgreSQL9.2 ?

Please read the release notes when updating to a new major version.
The first item under "Server Settings" in the 9.2 release notes is:

Silently ignore nonexistent schemas specified in search_path (Tom Lane)

This makes it more convenient to use generic path settings, which might 
include some schemas that don't exist in all databases.

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] Schema (Search path issue) on PostgreSQL9.2

2013-05-16 Thread Raghavendra

> postgres=# select version();
> version
>
>
> ---
>  PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
> 20080704 (Red Hat 4.1.2-52), 64-bit
> (1 row)
>
> postgres=# \dn
> List of schemas
> Name|  Owner
> +--
>  information_schema | postgres
>  pg_catalog | postgres
>  pg_toast   | postgres
>  pg_toast_temp_1| postgres
>  public | postgres
> (5 rows)
>
>
Apart from your actual question, am just curious to see this output, how
come all schema's displayed whereas in latest releases only PUBLIC schema
will be displayed if you use meta command \dn.

-bash-4.1$ ./psql -p 
psql (9.3beta1)
Type "help" for help.

postgres=# \dn
  List of schemas
  Name  |  Owner
+--
 public | postgres
(1 row)

--Raghav

Re: [GENERAL] Schema-only dump dumps no constraints, no triggers

2012-07-29 Thread Marek Kielar

Hi,


Dnia 28 lipca 2012 1:10 Adrian Klaver  napisał(a):

> What where the deleted files?
>WAL, Logs, other?
> What type of WAL replication are you doing?
>Streaming, log shipping, etc?
> What are your settings for the WAL replication?
>In particular wal_keep_segments ?
> Is the WAL replication actually working?


at this time - a couple days after restart, the clog hasn't re-formed yet. 
Thus, I am unable to tell you what files they were, we didn't pay that much 
attention to it then - there were some WAL files but I can't tell what the 
actual structure was. I'll provide this information whenever possible.

The WAL replication is a streaming replication with a hot standby server. The 
servers have a direct connection with one another. Configuration appended.

The replication is working fine.

The primary server also has a single mostly-"idle" transaction from any client 
node - a trait of an "always online" client application. Immediately after 
primary server restart this does not add much overhead, though. I can't tell 
whether this, in the long run, might cause the problem.

Configuration follows:
 - primary server postgresql.conf - WAL section
 - standby server postgresql.conf - WAL section
 - standby server recovery.conf

The primary server WAL configuration is:
#--
# WRITE AHEAD LOG
#--

# - Settings -

wal_level = hot_standby # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on# immediate fsync at commit
#wal_sync_method = fsync# the default is the first option 
# supported by the operating system:
#   open_datasync
#   fdatasync (default on Linux)
#   fsync
#   fsync_writethrough
#   open_sync
#full_page_writes = on  # recover from partial page writes
wal_buffers = 1MB   # min 32kB
# (change requires restart)
#wal_writer_delay = 200ms   # 1-1 milliseconds

#commit_delay = 0   # range 0-10, in microseconds
#commit_siblings = 5# range 1-1000

# - Checkpoints -

#checkpoint_segments = 30   # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min  # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s   # 0 disables

# - Archiving -

archive_mode = on   # allows archiving to be done
# (change requires restart)
archive_command = 'rsync %p /%f'   # command to use to archive a 
logfile segment
archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables

# - Streaming Replication -

max_wal_senders = 5 # max number of walsender processes
# (change requires restart)
#wal_sender_delay = 200ms   # walsender cycle time, 1-1 milliseconds
wal_keep_segments = 32  # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is delayed

# - Standby Servers -

#hot_standby = off  # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s# max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s  # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay




The standby server WAL configuration is:
#--
# WRITE AHEAD LOG
#--

# - Settings -

#wal_level = minimal# minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on# immediate fsync at commit
#wal_sync_method = fsync# the default is the first option 
# supported by the operating system:

Re: [GENERAL] Schema-only dump dumps no constraints, no triggers

2012-07-27 Thread Adrian Klaver

On 07/27/2012 09:32 AM, Marek Kielar wrote:

Hi, again,

I'm sorry about the lack of version information - I concentrated so much on
describing the problem correctly, that I forgot to provide basic information.
The version at locations is 9.1.4 (though this is irrelevant now), the server
is 9.0.4.

We found what the problem was. Another problem stems from it, however. Please
read on.

To add to the information already provided - we have a two-way backup of the template database. One
is a WAL replication and the other is londiste (skytools) replication with periodic complete copy.
As it turned out, the "stable" script uses not, as we remembered, the actual template
database but the londiste-replicated database which was to make next complete copy a few days ago.
The copy did not complete, however - the schema-table-column structure transfer completed, but the
constraints and triggers did not get through somehow, as there was a lack of hard drive space.
Digging on it, we found out that the drive's space was not used up by files in the filesystem, it
was filled with deleted files that postgresql server was still clinging on to, probably for a good
while. After restarting the server many, many gigabytes were suddenly made available on disk. And
this is the new problem - the server has quite a throughput and this is probably what causes the
"leakage". How can we forc

e
the server to let go of the files? Or maybe it is an actual leak that needs to
be studied upon?

On a side note, obviously, the Windows dump came out alright because it was
from the proper database, not the replicated copy.

What where the deleted files?
WAL, Logs, other?
What type of WAL replication are you doing?
Streaming, log shipping, etc?
What are your settings for the WAL replication?
In particular wal_keep_segments ?
Is the WAL replication actually working?

Best regards,
Marek Kielar

--
Adrian Klaver
adrian.kla...@gmail.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] Schema-only dump dumps no constraints, no triggers

2012-07-27 Thread Marek Kielar

Hi, again,

I'm sorry about the lack of version information - I concentrated so much on 
describing the problem correctly, that I forgot to provide basic information. 
The version at locations is 9.1.4 (though this is irrelevant now), the server 
is 9.0.4.

We found what the problem was. Another problem stems from it, however. Please 
read on.

To add to the information already provided - we have a two-way backup of the 
template database. One is a WAL replication and the other is londiste 
(skytools) replication with periodic complete copy. As it turned out, the 
"stable" script uses not, as we remembered, the actual template database but 
the londiste-replicated database which was to make next complete copy a few 
days ago. The copy did not complete, however - the schema-table-column 
structure transfer completed, but the constraints and triggers did not get 
through somehow, as there was a lack of hard drive space. Digging on it, we 
found out that the drive's space was not used up by files in the filesystem, it 
was filled with deleted files that postgresql server was still clinging on to, 
probably for a good while. After restarting the server many, many gigabytes 
were suddenly made available on disk. And this is the new problem - the server 
has quite a throughput and this is probably what causes the "leakage". How can 
we force the server to let go of the files? Or maybe it is an actual leak that 
needs to be studied upon?

On a side note, obviously, the Windows dump came out alright because it was 
from the proper database, not the replicated copy.

Best regards,
Marek Kielar

Dnia 27 lipca 2012 4:46 Adrian Klaver  napisał(a):

> On 07/26/2012 04:09 PM, Marek Kielar wrote:
> > Hi,
> >
> > we are using "pg_dump -s" (schema-only) to copy the structure of a 
> > template/prototype database as a set-up for several dozen Fedora boxes. The 
> > dump used to work alright until very recently. Now, across new machines 
> > that are to be introduced into the network it consistently refuses to dump 
> > constraints and triggers - seems pg_dump just skips over them. Otherwise 
> > the dump seems to be complete, the schema-table-column layout is complete. 
> > We thought it was the template server problem, but the oddity is that a 
> > dump made with Windows version of pgAdmin3 comes out complete.
> >
> > The command we use is:
> > /usr/bin/pg_dump -h  -p  -U  -F p -N 
> > '*somename*' -N 'somename2' -N 'somename3' -N 'somename4' -N 'somename5' -T 
> > '*somename6*' -s -v -f /some/dir/schemacopy.sql 
> >
> > The dump is made using a script we consider to be stable and therefore it 
> > hasn't changed since a long while ago. We also weren't able to pin down any 
> > other change between the systems where it previously worked and the ones 
> > where it now refuses to - the operating system (Fedora 16) is the same, the 
> > hardware is the same, the template database server is the same. It doesn't 
> > matter whether we are running the script on an up-to-date system or an 
> > outdated-off-liveCD-installation version, so it most probably is not 
> > update-related. The server (as a system) is sometimes under pretty much 
> > load so it might be resource-related - be it currently or previously.
> >
> > Searching through the archives, I have only stumbled upon a post from 2003 
> > about a similar issue 
> > (http://archives.postgresql.org/pgsql-admin/2003-08/msg00239.php) which 
> > might be connected, however, since the reporter gave up quickly, the issue 
> > remained unsolved.
> >
> > How can we dig into this further? What might be happening?
> 
> Postgres version?
> Is there more than one version of PG on machine?
> The dump made with PgAdmin uses the same parameters?
> Any errors in the logs on either the dump or restore side?
> 
> >
> > Best regards,
> > Marek Kielar
> >
> >
> 
> 
> 

-- 
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] Schema-only dump dumps no constraints, no triggers

2012-07-26 Thread Adrian Klaver


On 07/26/2012 04:09 PM, Marek Kielar wrote:

Hi,

we are using "pg_dump -s" (schema-only) to copy the structure of a 
template/prototype database as a set-up for several dozen Fedora boxes. The dump used to 
work alright until very recently. Now, across new machines that are to be introduced into 
the network it consistently refuses to dump constraints and triggers - seems pg_dump just 
skips over them. Otherwise the dump seems to be complete, the schema-table-column layout 
is complete. We thought it was the template server problem, but the oddity is that a dump 
made with Windows version of pgAdmin3 comes out complete.

The command we use is:
/usr/bin/pg_dump -h  -p  -U  -F p -N 
'*somename*' -N 'somename2' -N 'somename3' -N 'somename4' -N 'somename5' -T '*somename6*' -s -v -f 
/some/dir/schemacopy.sql 

The dump is made using a script we consider to be stable and therefore it 
hasn't changed since a long while ago. We also weren't able to pin down any 
other change between the systems where it previously worked and the ones where 
it now refuses to - the operating system (Fedora 16) is the same, the hardware 
is the same, the template database server is the same. It doesn't matter 
whether we are running the script on an up-to-date system or an 
outdated-off-liveCD-installation version, so it most probably is not 
update-related. The server (as a system) is sometimes under pretty much load so 
it might be resource-related - be it currently or previously.

Searching through the archives, I have only stumbled upon a post from 2003 
about a similar issue 
(http://archives.postgresql.org/pgsql-admin/2003-08/msg00239.php) which might 
be connected, however, since the reporter gave up quickly, the issue remained 
unsolved.

How can we dig into this further? What might be happening?


Postgres version?
Is there more than one version of PG on machine?
The dump made with PgAdmin uses the same parameters?
Any errors in the logs on either the dump or restore side?



Best regards,
Marek Kielar





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

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

1 2 3 >

1 - 100 of 237 matches

Mail list logo