Re: Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?

Thanks again for the reply.

So the approach would be, before the ETL starts, to change to UNLOGGED, and after it completes, change to LOGGED.

 

STeve

 

From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Wednesday, February 03, 2016 12:36 PM
To: Stephen Tahmosh <stahmosh@shieldsrx.com>
Cc: Keith <keith@keithf4.com>; pgsql-admin@postgresql.org; Albe Laurenz <laurenz.albe@wien.gv.at>

Subject: Re: [ADMIN] Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?

 

2016-02-03 18:19 GMT+01:00 Stephen Tahmosh <stahmosh@shieldsrx.com>:

Great suggestion to change to logged after ETL. 

What would happen if then changed it back to unlogged?  Would the data persist?  I would assume so.

 

 

In case of a crash, no, it would be completely truncated.
 

(I looked and did not see that info in http://www.postgresql.org/docs/9.5/static/sql-altertable.html)

 

 

From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Wednesday, February 03, 2016 12:09 PM
To: Keith <keith@keithf4.com>
Cc: pgsql-admin@postgresql.org; Stephen Tahmosh <stahmosh@shieldsrx.com>; Albe Laurenz <laurenz.albe@wien.gv.at>
Subject: Re: [ADMIN] Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?

 

Le 3 févr. 2016 5:30 PM, "Keith" <keith@keithf4.com> a écrit :
>
>
>
> On Wed, Feb 3, 2016 at 7:42 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>>
>> Stephen Tahmosh wrote:
>> > Is there any case where this data, in an unlogged table, would persist through a server “crash”?
>> > (however caused).
>>
>> No.
>> That is the price you pay for avoiding transaction logs.
>>
>> Yours,
>> Laurenz Albe
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>
>
> If you upgrade to 9.5, you can take advantage of the fact that you can convert an unlogged table to a logged one with an ALTER TABLE statement. I believe this does a full rewrite of the table again, but you may be able to incorporate that into your ETL process if it ends up still being quicker than doing the ETL directly on a normal table.
>
> http://www.postgresql.org/docs/9.5/static/sql-createtable.html#SQL-CREATETABLE-UNLOGGED 
>

It doesn't rewrite the whole table, but it puts its whole content in the WAL files.

THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the intended recipient, your use of this message for any purpose is strictly prohibited. If you have received this communication in error, please delete the message without making any copies and notify the sender so that we may correct our records. Thank you.

--

Guillaume.

  http://blog.guillaume.lelarge.info

  http://www.dalibo.com

THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the intended recipient, your use of this message for any purpose is strictly prohibited. If you have received this communication in error, please delete the message without making any copies and notify the sender so that we may correct our records. Thank you.