Re: [GENERAL] ID column naming convention

[Gavin Flower]

On 18/10/15 00:13, Karsten Hilbert wrote:

On Fri, Oct 16, 2015 at 04:59:12PM -0500, Jim Nasby wrote:


BTW, I found Karsten's idea of using 'pk' for the surrogate key, and
fk_table_name interesting. It helps avoid ambiguity from externally
generated ID values.

That's the point :-)

Here's a real live schema using (mostly) the above approach:

http://www.gnumed.de/~ncq/gnumed/schema/gnumed_v20/

in case anyone is interested in taking a look.

Karsten

Hmm...

Apparently (according to your naming convention) several tables (such as 
'clin.substance_intake') have 2 PRIMARY KEYs!!!


I guess you must have a large wall to display the schema on!

Looks like a lot of fun.

Can you tell us more about the database (ignoring nit-picking!)?


Cheers,
Gavin


--
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] ID column naming convention

[Scott Mead]

> On Oct 13, 2015, at 18:27, droberts  wrote:
> 
> Gavin Flower-2 wrote
>>> On 14/10/15 06:36, droberts wrote:
>>> Hi, is there a problem calling ID's different when used as a FK vs table
>>> ID?
>>> For example
>>> 
>>> 
>>> mydimtable ()
>>>  ID
>>>  name
>>>  description
>>> 
>>> 
>>> myfacttable ()
>>>   my_dim_id   # FK to ID above
>>>   total_sales
>>> 
>>> 
>>> I 'think' if I don't enforce foreign key constraints, then this practice
>>> prevents tools from being able to generate ERD diagrams right?
>>> 
>>> 
>>> 
>>> --
>>> View this message in context:
>>> http://postgresql.nabble.com/ID-column-naming-convention-tp5869844.html
>>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>> My practice is to name the PRIMARY KEY as id, and foreign keys with the 
>> original table name plus the sufiix_id.
>> 
>> By leaving the table name off the primary key name, and just using id, 
>> makes it more obvious that it is a primary key (plus it seems redundant 
>> to prefix the primary key name with its own table name!).
>> 
>> CREATE TABLE house
>> (
>> id  int PRIMARY KEY,
>> address text
>> );
>> 
>> CREATE TABLE room
>> (
>> id   int PRIMARY KEY,
>> house_id int REFERENCES house(id),
>> name text
>> );
>> 
>> 
>> There are exceptions like:
>> 
>> CREATE TABLE human
>> (
>> idint PRIMARY KEY,
>> mother_id int REFERENCES human (id),
>> father_id int REFERENCES human (id),
>> name  text
>> );
>> 
>> Cheers,
>> Gavin
>> 
>> 
>> -- 
>> Sent via pgsql-general mailing list (
> 
>> pgsql-general@
> 
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
> 
> Thanks.   My only question is how do you create a schema diagram (ERD) then? 
> The tool won't know what the relationships are unless maybe you put foreign
> key constraints on.  
That's how most tools work, usually by calling the driver api (jdbc 
databasemetadata, etc) which in turn look at the information_schema. If you 
don't setup real referential integrity, any tool that can use names is just 
guessing   

   I think dbvisualizer will 'infer' based on column names.  I KNOW that 
schemaspy has this option, but they explicitly note it is a GUESS. 

   Use foreign keys. 



> BTW does anyone recommend a tool to to that?  I've been
> playing with DbVisualizer.
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/ID-column-naming-convention-tp5869844p5869881.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


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

[GENERAL] Recommendations for migrating PG 9.3 RDS across regions

[Joshua Ma]

I'm currently looking into how we might migrate an AWS-hosted database (via
RDS) from us-west-2 to us-east-1. This isn't natively supported by RDS
right now, so I was wondering if anyone has had success with 3rd-party
tools. (I'm also on 9.3, unfortunately, so I lack some of 9.4's replication
features.)

I can always do a pg_dump and pg_restore, but ideally we do some sort of
streaming replication to minimize downtime. Ideally, we'd set up
replication, turn off writes to the primary, promote the replica, flip DNS,
and resume with newly promoted replica as primary. The DB we're migrating
is ~100GB in size - not too huge, but large enough for pg_dump to take
awhile.

For starters, will I run into issues with Londiste, Slony, or Bucardo,
since RDS has more restricted permissions?

Is one of Londiste, Slony, or Bucardo obviously better than the others for
this task? At first glance Bucardo seems the most straightforward, but that
may just be due to the docs being easier to glance through. (I've also
never used any postgres replication outside of the native replication, so
it's possible that some of these tools aren't applicable...)

Any tips/links would be much appreciated.

Best,
Josh
ᐧ

Re: [GENERAL] question

[anj patnaik]

My question is for Francisco who replied regarding xz. I was curious what
options he used. Thanks.

On Fri, Oct 16, 2015 at 3:14 PM, Adrian Klaver 
wrote:

> On 10/16/2015 12:10 PM, anj patnaik wrote:
>
>> Thanks. what is the recommended command/options for backup and how to
>> restore?
>>
>> I found the below online. let me know if this is better and how to
>> restore. Thank you
>>
>> pg_dump -Fc  '' | xz -3 dump.xz
>>
>
> Again, why would compress an already compressed output?
>
> Also online:
>
> http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
>
> http://www.postgresql.org/docs/9.4/interactive/app-pgrestore.html
>
> They step you through the backup and restore process.
>
>>
>>
>> On Fri, Oct 16, 2015 at 4:05 AM, Francisco Olarte
>> mailto:fola...@peoplecall.com>> wrote:
>>
>> On Fri, Oct 16, 2015 at 8:27 AM, Guillaume Lelarge
>> mailto:guilla...@lelarge.info>> wrote:
>> > 2015-10-15 23:05 GMT+02:00 Adrian Klaver > >:
>> >> On 10/15/2015 01:35 PM, anj patnaik wrote:
>> ...
>> >>> ./pg_dump -t RECORDER  -Fc postgres |  gzip > /tmp/dump
>> >>> Are there any other options for large tables to run faster and
>> occupy
>> >>> less disk space?
>> >> Yes, do not double compress. -Fc already compresses the file.
>> > Right. But I'd say "use custom format but do not compress with
>> pg_dump". Use
>> > the -Z0 option to disable compression, and use an external
>> multi-threaded
>> > tool such as pigz or pbzip2 to get faster and better compression.
>>
>> Actually I would not recommend that, unless you are making a long term
>> or offsite copy. Doing it means you need to decompress the dump before
>> restoring or even testing it ( via i.e., pg_restore > /dev/null ).
>>
>> And if you are pressed on disk space you may corner yourself using
>> that on a situation where you do NOT have enough disk space for an
>> uncompressed dump. Given you normally are nervous enough when
>> restoring, for normal operations I think built in compression is
>> better.
>>
>> Also, I'm not current with the compressor Fc uses, I think it still is
>> gzip, which is not that bad and is normally quite fast ( In fact I do
>> not use that 'pbzip2', but I did some tests about a year ago and I
>> found bzip2 was beaten by xz quite easily ( That means on every level
>> of bzip2 one of the levels of xz beat it in BOTH size & time, that was
>> for my data, YMMV  ).
>>
>>
>> Francisco Olarte.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] question

[anj patnaik]

Thanks. what is the recommended command/options for backup and how to
restore?

I found the below online. let me know if this is better and how to restore.
Thank you

pg_dump -Fc  '' | xz -3 dump.xz


On Fri, Oct 16, 2015 at 4:05 AM, Francisco Olarte 
wrote:

> On Fri, Oct 16, 2015 at 8:27 AM, Guillaume Lelarge
>  wrote:
> > 2015-10-15 23:05 GMT+02:00 Adrian Klaver :
> >> On 10/15/2015 01:35 PM, anj patnaik wrote:
> ...
> >>> ./pg_dump -t RECORDER  -Fc postgres |  gzip > /tmp/dump
> >>> Are there any other options for large tables to run faster and occupy
> >>> less disk space?
> >> Yes, do not double compress. -Fc already compresses the file.
> > Right. But I'd say "use custom format but do not compress with pg_dump".
> Use
> > the -Z0 option to disable compression, and use an external multi-threaded
> > tool such as pigz or pbzip2 to get faster and better compression.
>
> Actually I would not recommend that, unless you are making a long term
> or offsite copy. Doing it means you need to decompress the dump before
> restoring or even testing it ( via i.e., pg_restore > /dev/null ).
>
> And if you are pressed on disk space you may corner yourself using
> that on a situation where you do NOT have enough disk space for an
> uncompressed dump. Given you normally are nervous enough when
> restoring, for normal operations I think built in compression is
> better.
>
> Also, I'm not current with the compressor Fc uses, I think it still is
> gzip, which is not that bad and is normally quite fast ( In fact I do
> not use that 'pbzip2', but I did some tests about a year ago and I
> found bzip2 was beaten by xz quite easily ( That means on every level
> of bzip2 one of the levels of xz beat it in BOTH size & time, that was
> for my data, YMMV  ).
>
>
> Francisco Olarte.
>

Re: [GENERAL] question

[Francisco Olarte]

Hi Anj:

On Sat, Oct 17, 2015 at 3:11 AM, anj patnaik  wrote:
> My question is for Francisco who replied regarding xz. I was curious what
> options he used. Thanks.

1st, we do not normally top post on this list.

Second, I do not remember the exact options I use. I can look them up,
but they are going to be surely useless ( they are for a custom
process with does several things with files, it uses gzip ( in Fc
backups ), plus xz ( for some files which need to be kept for a long
time and are nearly never needed ), plus lzo ( as I found lzo
compressed temporary files were faster than uncompressed ones ), and a
lot of code. But in the development process we did a full comparison
of several compressor, and found what I stated with bzip2, it was
surpassed in every combination of options by xz ( plain bzip2, plain
xz ).

Francisco Olarte.


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

Re: [GENERAL] question

[Francisco Olarte]

Hi Anj:

On Thu, Oct 15, 2015 at 10:35 PM, anj patnaik  wrote:

>
> I will experiment with -Fc (custom). The file is already growing very
> large.
>

​I do not recall if you've already provided them, but, how large? I mean,
if you have a large database, backup will take time and ocupy space, you
may be approaching.

As a benchmark, for intellectual satisfaction, the smallest backup you can
get is probably text format and then compress with the more agressive
option of your favorite compressor, but this is normally useless except for
very special cases.

My recomendation will be to use plain Fc for a backup, this is what I do.
Sometimes tweaking the -Z after tests, but normally in my experience the
default level is right. bear in mind DB disk tend to be expensive, backup
disks can be much cheaper and, unless you are keeping a lot of them,
backups are smaller. As an example, we have a server pair ( replicated ),
with a couple short stroked fast disks for the database and a couple
'normal' disks for first line backup in each one. Normal disks are about
ten times database disks, and easily fit 30 backups, so we can  backup to
one of them, copy to the seconds, and replicate to the other in the server
pair, just using Fc. This because backup compress indexes quite well, by
reducing them to a 'CREATE INDEX', and the copy format used inside is
generally more compact than the layout used on disk ( which needs free
space, is framed and lot of other things ) and compresses quite well too.
If you are pressed for backup size, you normally have very special needs or
do not have a properly dimensioned system. But, to say anything more you
will need to provide some numbers ( how big is your database and backups,
how fast are you disks and things like this. In this case maybe hints can
be provided.


>
> I am running this:
> ./pg_dump -t RECORDER  -Fc postgres |  gzip > /tmp/dump
>

​In this case gzip is useless. -Fc already uses gzip compression​ at the
member level. Doing it with -Z0 and then gzipping will gain you a bit,
obvously, as it will compress everything as a single chunk ( except if you
manage to hit a pathological case ), but I doubt it will be significant .

As pointed in other places you can use Fc+Z0 and then compress with a
'better' compresor you may get a smaller file, or get it faster, but
remember you'll need to decompress it before restoring ( this does not
happen for text format, as you can do stream restore, but the restore
options for text format are limited, it's an all or nothing approach unless
you are really fluent in stream editors ).

Francisco Olarte.

Re: [GENERAL] ID column naming convention

[Karsten Hilbert]

On Fri, Oct 16, 2015 at 04:59:12PM -0500, Jim Nasby wrote:

> BTW, I found Karsten's idea of using 'pk' for the surrogate key, and
> fk_table_name interesting. It helps avoid ambiguity from externally
> generated ID values.

That's the point :-)

Here's a real live schema using (mostly) the above approach:

http://www.gnumed.de/~ncq/gnumed/schema/gnumed_v20/

in case anyone is interested in taking a look.

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