On 8/18/20 12:35 PM, Jason Myers wrote:
> Postgres 12.4
>
> I was directed in slack to mention here that we're being impacted by
> Postgres leaving orphaned pages in /base/<db> after a crash while a
> CREATE TABLE is being run in transaction.
>
> The issue is the same as the reproduction steps listed here [1], that is:
>
> - Start a CREATE TABLE transaction for a large table
> - Terminate the process via kill -9
> - Pages are left in /base that have no filenode references anymore, such
> that `pg_database_size()` grows to be very large while total table+index
> size remains constant
So from [1] you are using CREATE TABLE AS. Have you tried with:
BEGIN;
CREATE TABLE some_table SELECT some_data FROM other_table LIMIT 1 WITH
NO DATA;
COMMIT;
The above gets you the table structure, but no data.
BEGIN;
INSERT into some_table SELECT * FROM other_table;
COMMIT;
The above populates the table. Have not tested but I'm going to assume
if you kill the above the problem would not happen or would be fixable
by DELETE FROM some_table/TRUNCATE some_table;
>
> However in our particular case, we're using a managed/cloud Postgres
> server and our `CREATE TABLE` transaction was being terminated by the
> OOM killer. Using a managed service, we don't have filesystem access to
> go and clear out these orphaned pages. This caused our total db size to
> grow from 40GB of table+index data to 4TB on-disk (but still only 40GB
> of table+index data, the other ~3.95TB being orphaned CREATE TABLE pages)
>
> I realize (per a blog post from Robert Haas [2] and from slack
> conversation) that this is a known issue, but was directed here from
> slack to just mention that we were impacted by it, and have no
> resolution due to not having filesystem access, and not having a method
> internally to Postgres to deal with these orphaned relations. (Our
> recourse currently is to do something like a pg_dump/pg_restore onto a
> clean instance in order to escape the orphaned files)
>
> -Jason
>
> [1] https://github.com/bdrouvot/pg_orphaned#example-1
> [2]
> https://rhaas.blogspot.com/2020/05/dont-manually-modify-postgresql-data.html
>
--
Adrian Klaver
adrian.klaver@aklaver.com