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

2016-02-03 18:45 GMT+01:00 Stephen Tahmosh <stahmosh(at)shieldsrx(dot)com>:

> Unless you do many transformations to the same data,
>
>
>
> Yes, that was the thought
>
>
>

Then, you should at least benchmark it. That would give you a better
picture if it would work for you. But that's an interesting option for you,
I think.

STeve
>
> *From:* Guillaume Lelarge [mailto:guillaume(at)lelarge(dot)info]
> *Sent:* Wednesday, February 03, 2016 12:43 PM
> *To:* Stephen Tahmosh <stahmosh(at)shieldsrx(dot)com>
> *Cc:* pgsql-admin(at)postgresql(dot)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(at)shieldsrx(dot)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(at)lelarge(dot)info]
> *Sent:* Wednesday, February 03, 2016 12:36 PM
> *To:* Stephen Tahmosh <stahmosh(at)shieldsrx(dot)com>
> *Cc:* Keith <keith(at)keithf4(dot)com>; pgsql-admin(at)postgresql(dot)org; Albe Laurenz
> <laurenz(dot)albe(at)wien(dot)gv(dot)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(at)shieldsrx(dot)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(at)lelarge(dot)info]
> *Sent:* Wednesday, February 03, 2016 12:09 PM
> *To:* Keith <keith(at)keithf4(dot)com>
> *Cc:* pgsql-admin(at)postgresql(dot)org; Stephen Tahmosh <stahmosh(at)shieldsrx(dot)com>;
> Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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(at)keithf4(dot)com> a écrit :
> >
> >
> >
> > On Wed, Feb 3, 2016 at 7:42 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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(at)postgresql(dot)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.
>
>
>
>
> --
>
> 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.
>

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com