{"id":13007,"date":"2019-11-14T08:24:57","date_gmt":"2019-11-14T07:24:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/"},"modified":"2023-02-15T12:54:05","modified_gmt":"2023-02-15T11:54:05","slug":"can-there-be-orphaned-data-files-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/","title":{"rendered":"Can there be orphaned data files in PostgreSQL?"},"content":{"rendered":"

PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the writing the write ahead log<\/a> or by synchronization the data files on disk whenever there is a checkpoint. When it comes to the data files PostgreSQL creates a new one once a relation reaches segment_size<\/a>, which is 1GB by default. Because of this a relation can consist of many files if the relation is growing. The question I want to look at in this post is, if there can be orphaned files. A file would be orphaned if it is not part\/referenced by any relation but the file is still there on disk. That would be a waste of disk space on the one hand but it would also be inconsistent with what is stored in PostgreSQL catalog.<\/p>\n

<\/p>\n

To start with, lets create an empty table:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>create<\/code> table<\/code> t1 ( a <\/code>int<\/code> );<\/code><\/div>\n
CREATE<\/code> TABLE<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

This already created a file on disk and we can ask PostgreSQL which file that actually is:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> pg_relation_filepath(<\/code>'t1'<\/code>);<\/code><\/div>\n
\u00a0<\/code>pg_relation_filepath <\/code><\/div>\n
----------------------<\/code><\/div>\n
\u00a0<\/code>base\/12724\/24577<\/code><\/div>\n
(1 row)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Of course this file is also there on the file system and standard operating system utilities can be used to verify this:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
postgres@centos8pg:<\/code>\/u02\/pgdata\/12\/<\/code> [121] <\/code>ls<\/code> -la $PGDATA<\/code>\/base\/12724\/24577<\/code><\/div>\n
-rw-------. 1 postgres postgres 0 Nov 13 16:53 <\/code>\/u02\/pgdata\/12\/base\/12724\/24577<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Because there is noting in the table yet, the file is empty. PostgreSQL comes with a small utility called oid2name<\/a> which also can be used to check the location on disk for a given relation:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
postgres@centos8pg:<\/code>\/u02\/pgdata\/12\/<\/code> [121] oid2name -t t1 -x<\/code><\/div>\n
From database <\/code>\"postgres\"<\/code>:<\/code><\/div>\n
\u00a0\u00a0<\/code>Filenode\u00a0 Table Name\u00a0\u00a0\u00a0 Oid\u00a0 Schema\u00a0 Tablespace<\/code><\/div>\n
-------------------------------------------------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>24577\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t1\u00a0 24577\u00a0 public\u00a0 pg_default<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Once we populate the table with data the file will be growing and once it reaches the segment_site<\/a> PostgreSQL will add another file to the relation (please notice that my segment_size is 2GB, instead of the default of 1GB):<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>insert<\/code> into<\/code> t1 <\/code>select<\/code> * <\/code>from<\/code> generate_series(1,1000000);<\/code><\/div>\n
INSERT<\/code> 0 1000000<\/code><\/div>\n
postgres=# ! ls -lha $PGDATA\/base\/12724\/24577<\/code><\/div>\n
-rw<\/code>-------. 1 postgres postgres 35M Nov 13 17:03 \/u02\/pgdata\/12\/base\/12724\/24577<\/code><\/div>\n
postgres=# <\/code>insert<\/code> into<\/code> t1 <\/code>select<\/code> * <\/code>from<\/code> generate_series(1,100000000);<\/code><\/div>\n
INSERT<\/code> 0 100000000<\/code><\/div>\n
postgres=# ! ls -la $PGDATA\/base\/12724\/24577*<\/code><\/div>\n
-rw<\/code>-------. 1 postgres postgres 2147483648 Nov 13 17:07 \/u02\/pgdata\/12\/base\/12724\/24577<\/code><\/div>\n
-rw<\/code>-------. 1 postgres postgres 1513545728 Nov 13 17:08 \/u02\/pgdata\/12\/base\/12724\/24577.1<\/code><\/div>\n
-rw<\/code>-------. 1 postgres postgres\u00a0\u00a0\u00a0\u00a0 917504 Nov 13 17:07 \/u02\/pgdata\/12\/base\/12724\/24577_fsm<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The “*_fsm” file is the free space map<\/a> which will not be discussed here (it is used to track the available space in the data files).<\/p>\n

Now that it is clear what happens on disk we’ll come back to the initial question of this post: Can it happen that files on disk do not belong to any relation? Consider this example: In one session we start a new transaction and create an empty table without committing the transaction. Additionally we get the PID of the session and the location on disk:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>begin<\/code>;<\/code><\/div>\n
BEGIN<\/code><\/div>\n
postgres=# <\/code>create<\/code> table<\/code> t2 ( a <\/code>int<\/code> );<\/code><\/div>\n
CREATE<\/code> TABLE<\/code><\/div>\n
postgres=# <\/code>select<\/code> pg_relation_filepath(<\/code>'t2'<\/code>);<\/code><\/div>\n
\u00a0<\/code>pg_relation_filepath <\/code><\/div>\n
----------------------<\/code><\/div>\n
\u00a0<\/code>base\/12724\/24580<\/code><\/div>\n
(1 row)<\/code><\/div>\n
<\/div>\n
postgres=# <\/code>select<\/code> * <\/code>from<\/code> pg_backend_pid();<\/code><\/div>\n
\u00a0<\/code>pg_backend_pid <\/code><\/div>\n
----------------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>7170<\/code><\/div>\n
(1 row)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Without committing we can already check the file on disk, as PostgreSQL already created it:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
postgres=# ! ls -la $PGDATA\/base\/12724\/24580<\/code><\/div>\n
-rw<\/code>-------. 1 postgres postgres 0 Nov 13 17:17 \/u02\/pgdata\/12\/base\/12724\/24580<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

If the server is crashing now, or the out of memory killer<\/a> kicks in and kills the session: what happens then? We can simulate that quite easy by killing the session without giving it any chance to cleanup (which is signal 9):<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
postgres@centos8pg:<\/code>\/home\/postgres\/<\/code> [121] <\/code>kill<\/code> -9 7170<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Going back to the psql session it lost the connection but then immediately reconnects. :<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
postgres-# <\/code>select<\/code> 1;<\/code><\/div>\n
server closed the <\/code>connection<\/code> unexpectedly<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>This probably means the server terminated abnormally<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>before <\/code>or<\/code> while processing the request.<\/code><\/div>\n
The <\/code>connection<\/code> to<\/code> the server was lost. Attempting reset: Succeeded.<\/code><\/div>\n
postgres=# <\/code>select<\/code> 1;<\/code><\/div>\n
\u00a0<\/code>?<\/code>column<\/code>? <\/code><\/div>\n
----------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>1<\/code><\/div>\n
(1 row)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

To summarize: The session was killed before the transaction was able to complete and the transaction already created a table. What must happen is, that the transaction is rolled back and the table must not be there:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> * <\/code>from<\/code> t2;<\/code><\/div>\n
ERROR:\u00a0 relation <\/code>\"t2\"<\/code> does <\/code>not<\/code> exist<\/code><\/div>\n
LINE 1: <\/code>select<\/code> * <\/code>from<\/code> t2;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

This is fine and what is expected. But do we still see the file on disk?<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
postgres@centos8pg:<\/code>\/home\/postgres\/<\/code> [121] <\/code>ls<\/code> -lha $PGDATA<\/code>\/base\/12724\/24580<\/code><\/div>\n
-rw-------. 1 postgres postgres 0 Nov 13 17:17 <\/code>\/u02\/pgdata\/12\/base\/12724\/24580<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Yes, now we really have an orphaned file which does not belong to any relation PostgreSQL knows of:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> relname <\/code>from<\/code> pg_class <\/code>where<\/code> oid = <\/code>'24580'<\/code>;<\/code><\/div>\n
\u00a0<\/code>relname <\/code><\/div>\n
---------<\/code><\/div>\n
(0 <\/code>rows<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

So there might be cases when you are required to cleanup the files on disk. Image you did huge load like this and just before the load completes the session is killed:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>begin<\/code>;<\/code><\/div>\n
BEGIN<\/code><\/div>\n
postgres=# <\/code>create<\/code> table<\/code> t3 ( a <\/code>int<\/code> );<\/code><\/div>\n
CREATE<\/code> TABLE<\/code><\/div>\n
postgres=# <\/code>select<\/code> pg_relation_filepath(<\/code>'t3'<\/code>);<\/code><\/div>\n
\u00a0<\/code>pg_relation_filepath <\/code><\/div>\n
----------------------<\/code><\/div>\n
\u00a0<\/code>base\/12724\/32769<\/code><\/div>\n
(1 row)<\/code><\/div>\n
<\/div>\n
postgres=# <\/code>select<\/code> * <\/code>from<\/code> pg_backend_pid();<\/code><\/div>\n
\u00a0<\/code>pg_backend_pid <\/code><\/div>\n
----------------<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>7577<\/code><\/div>\n
(1 row)<\/code><\/div>\n
<\/div>\n
postgres=# <\/code>insert<\/code> into<\/code> t3 <\/code>select<\/code> * <\/code>from<\/code> generate_series(1,10000000);<\/code><\/div>\n
server closed the <\/code>connection<\/code> unexpectedly<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>This probably means the server terminated abnormally<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>before <\/code>or<\/code> while processing the request.<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Now the file is not empty anymore and disk space is consumed that is for nothing:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
postgres@centos8pg:<\/code>\/home\/postgres\/<\/code> [121] <\/code>ls<\/code> -lha $PGDATA<\/code>\/base\/12724\/32769<\/code><\/div>\n
-rw-------. 1 postgres postgres 235M Nov 13 17:42 <\/code>\/u02\/pgdata\/12\/base\/12724\/32769<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

In the worst case that could be gigabytes or terabytes of wasted space. Is there at least a way to detect such orphaned files? You would need to compare what PostgreSQL has recorded in the catalog against the file system and then delete everything PostgreSQL is not aware of and this needs to be done very, very carefully.<\/p>\n

First of all you’d need to get the OID of the database you want to check:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> oid <\/code>from<\/code> pg_database <\/code>where<\/code> datname = <\/code>'postgres'<\/code>;<\/code><\/div>\n
\u00a0\u00a0<\/code>oid\u00a0 <\/code><\/div>\n
-------<\/code><\/div>\n
\u00a0<\/code>12724<\/code><\/div>\n
(1 row)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Once you have that you know the location on disk, which is $PGDATA\/base\/[OID_OF_THE_DATABASE] (I am not considering tablespaces here). From here on you can list all the files that should belong to a relation (the regular expression is testing for numbers as only those are important here):<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> * <\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>from<\/code> pg_ls_dir ( <\/code>'\/u02\/pgdata\/12\/base\/12724'<\/code> ) <\/code>as<\/code> file <\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>where<\/code> file\u00a0 ~ '^[0-9]*<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

All of these should have entry in pg_class<\/a> (otherwise PostgreSQL is not aware of them).<\/p>\n

Finally, getting the list of orphaned files:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> * <\/code>from<\/code> pg_ls_dir ( <\/code>'\/u02\/pgdata\/12\/base\/12724'<\/code> ) <\/code>as<\/code> file <\/code>where<\/code> file ~ '^[0-9]*<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

That would be the files you need to check carefully. If you are sure it is an orphaned file you may delete it (after you make a backup, of course).<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n<\/td>\n
\n
\n
;<\/code><\/div>\n
<\/div>\n
\u00a0<\/code>file\u00a0 <\/code><\/div>\n
-------<\/code><\/div>\n
\u00a0<\/code>1255<\/code><\/div>\n
\u00a0<\/code>1247<\/code><\/div>\n
\u00a0<\/code>1249<\/code><\/div>\n
\u00a0<\/code>1259<\/code><\/div>\n
...<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

All of these should have entry in pg_class<\/a> (otherwise PostgreSQL is not aware of them).<\/p>\n

Finally, getting the list of orphaned files:<\/p>\n

<\/pre>\n
That would be the files you need to check carefully. If you are sure it is an orphaned file you may delete it (after you make a backup, of course).<\/p>\n
\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
and<\/code> file::text <\/code>not<\/code> in<\/code> (<\/code>select<\/code> oid::text <\/code>from<\/code> pg_class ); file <\/code>------- 16385 24580 32769 (3 rows)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n
That would be the files you need to check carefully. If you are sure it is an orphaned file you may delete it (after you make a backup, of course).<\/p>\n
; file ——- 1255 1247 1249 1259 …<\/p>\n
All of these should have entry in pg_class<\/a> (otherwise PostgreSQL is not aware of them).<\/p>\n
Finally, getting the list of orphaned files:<\/p>\n
<\/pre>\n
That would be the files you need to check carefully. If you are sure it is an orphaned file you may delete it (after you make a backup, of course).<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the writing the write ahead log or by synchronization the data files on disk whenever there is a checkpoint. When it comes to the data files PostgreSQL creates a new one once a relation reaches segment_size, which is 1GB […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-13007","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nCan there be orphaned data files in PostgreSQL? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can there be orphaned data files in PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the writing the write ahead log or by synchronization the data files on disk whenever there is a checkpoint. When it comes to the data files PostgreSQL creates a new one once a relation reaches segment_size, which is 1GB […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-14T07:24:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-15T11:54:05+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Can there be orphaned data files in PostgreSQL?\",\"datePublished\":\"2019-11-14T07:24:57+00:00\",\"dateModified\":\"2023-02-15T11:54:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\"},\"wordCount\":868,\"commentCount\":1,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\",\"name\":\"Can there be orphaned data files in PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-11-14T07:24:57+00:00\",\"dateModified\":\"2023-02-15T11:54:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can there be orphaned data files in PostgreSQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Can there be orphaned data files in PostgreSQL? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Can there be orphaned data files in PostgreSQL?","og_description":"PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the writing the write ahead log or by synchronization the data files on disk whenever there is a checkpoint. When it comes to the data files PostgreSQL creates a new one once a relation reaches segment_size, which is 1GB […]","og_url":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2019-11-14T07:24:57+00:00","article_modified_time":"2023-02-15T11:54:05+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Can there be orphaned data files in PostgreSQL?","datePublished":"2019-11-14T07:24:57+00:00","dateModified":"2023-02-15T11:54:05+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/"},"wordCount":868,"commentCount":1,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/","name":"Can there be orphaned data files in PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-11-14T07:24:57+00:00","dateModified":"2023-02-15T11:54:05+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-there-be-orphaned-data-files-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can there be orphaned data files in PostgreSQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13007","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=13007"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13007\/revisions"}],"predecessor-version":[{"id":22637,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13007\/revisions\/22637"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13007"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}