Thread: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
Hi,
The goal was to minimize log activity during ETL. The assumption was, once a Table load was “committed” the data would persist after a system shutdown.
Our database backup currently “aborts” the server (working to change to an orderly shutdown).
I have loaded data to an unlogged table, and committed on completion.
I am able to see the data in another session, so I am sure the data has been persisted.
Over night, our database is shut down (forced) for a backup. The next morning, I find that the data in my unlogged table is gone.
Is there any case where this data, in an unlogged table, would persist through a server “crash”? (however caused).
Thanks,
STeve
Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
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
Re: Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
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
Re: 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.
Re: Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
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.
(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.
Re: Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
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.
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.(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.
--
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.
--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.
--
Re: Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
Unless you do many transformations to the same data,
Yes, that was the thought
STeve
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Wednesday, February 03, 2016 12:43 PM
To: Stephen Tahmosh <stahmosh@shieldsrx.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
2016-02-03 18:39 GMT+01:00 Stephen Tahmosh <stahmosh@shieldsrx.com>:
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.
That could work, though I don't quite understand what you gain by doing so. Having a table unlogged helps you to lower writes to the WAL, and making it logged afterwards just put everything back to WAL. Unless you do many transformations to the same data, there's no point for this.
PS : keep the mailing list in the loop.
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.
--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.
--
Re: Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
Unless you do many transformations to the same data,
Yes, that was the thought
STeve
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Wednesday, February 03, 2016 12:43 PM
To: Stephen Tahmosh <stahmosh@shieldsrx.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Re: Postgres 9.4 unlogged table. Under any circumstance after server crash would data persist?
2016-02-03 18:39 GMT+01:00 Stephen Tahmosh <stahmosh@shieldsrx.com>:
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.
That could work, though I don't quite understand what you gain by doing so. Having a table unlogged helps you to lower writes to the WAL, and making it logged afterwards just put everything back to WAL. Unless you do many transformations to the same data, there's no point for this.
PS : keep the mailing list in the loop.
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.
--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.
--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.
--