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@xxxxxxxxxxx
