Backing up a postgres database is a critical task for every database administrator. While many users create backups using the TAR format, few take the time to understand what is actually stored inside the archive. A TAR backup is more than just a compressed file. It contains the database objects, metadata, and data files required to rebuild the database during restoration.
Knowing how to inspect a TAR backup can be useful in several situations. You might want to verify that the backup was created successfully, examine the database objects it contains, or simply understand how postgres organizes backup data internally without performing a full restore.
Now, let’s back up the Odoo database in .tar format like this.
pg_dump -U odoo -h localhost -F t -f /tmp/odoo_backup.tar odoo
Check the contents inside /tmp folder.
ls /tmp/odoo_backup.tar
Result:
/tmp/odoo_backup.tar
Check the file size to ensure the backup contains data.
ls -lh /tmp/odoo_backup.tar
Result:
-rw-rw-r-- 1 postgres postgres 644M Sep 5 11:08 /tmp/odoo_backup.tar
Here, we can see the backup file size. Now, we can ensure that the file is not empty.
Now, use the command below to list the names of all files stored inside the tar archive without extracting them.
The | head part displays only the first 10 entries from that list, making it easier to preview the archive contents.
tar -tf /tmp/odoo_backup.tar | head
Result:
toc.dat
10816.dat
10820.dat
10925.dat
10819.dat
10818.dat
10814.dat
10856.dat
10838.dat
10875.dat
Now, use the pg_restore command to see the contents of the .tar file like this.
pg_restore -l /tmp/odoo_backup.tar
Result:
803; 1259 179333 SEQUENCE public account_report_line_id_seq odoo
12020; 0 0 SEQUENCE OWNED BY public account_report_line_id_seq odoo
802; 1259 179325 TABLE public account_report_section_rel odoo
12021; 0 0 COMMENT public TABLE account_report_section_rel odoo
824; 1259 179467 TABLE public account_resequence_wizard odoo
12022; 0 0 COMMENT public TABLE account_resequence_wizard odoo
12023; 0 0 COMMENT public COLUMN account_resequence_wizard.create_uid odoo
12024; 0 0 COMMENT public COLUMN account_resequence_wizard.write_uid odoo
12025; 0 0 COMMENT public COLUMN account_resequence_wizard.first_name odoo
12026; 0 0 COMMENT public COLUMN account_resequence_wizard.ordering odoo
12027; 0 0 COMMENT public COLUMN account_resequence_wizard.first_date odoo
12028; 0 0 COMMENT public COLUMN account_resequence_wizard.end_date odoo
12029; 0 0 COMMENT public COLUMN account_resequence_wizard.create_date odoo
12030; 0 0 COMMENT public COLUMN account_resequence_wizard.write_date odoo
Understanding Sequence, Table, and Comment Entries
The beginning of the archive list contains entries such as SEQUENCE, TABLE, and COMMENT. These entries represent the database objects that Postgres recreates during the restore process.
A SEQUENCE generates unique values, and it is usually for columns like id. The SEQUENCE OWNED BY entry links the sequence to a specific table column.
TABLE entries define the structure of the database tables, including their columns and properties. COMMENT entries restore any descriptive text added to tables or columns.
We can see each table comment using the command below
\d+ table_name
This is the second part when we read the contents of the tar file using pg_restore.
5820; 0 0 TABLE ATTACH public sale_order_default odoo
5809; 0 0 TABLE ATTACH public sale_order_p20200101 odoo
5810; 0 0 TABLE ATTACH public sale_order_p20210101 odoo
5811; 0 0 TABLE ATTACH public sale_order_p20220101 odoo
5812; 0 0 TABLE ATTACH public sale_order_p20230101 odoo
5813; 0 0 TABLE ATTACH public sale_order_p20240101 odoo
5814; 0 0 TABLE ATTACH public sale_order_p20250101 odoo
5815; 0 0 TABLE ATTACH public sale_order_p20260101 odoo
5816; 0 0 TABLE ATTACH public sale_order_p20270101 odoo
5817; 0 0 TABLE ATTACH public sale_order_p20280101 odoo
5818; 0 0 TABLE ATTACH public sale_order_p20290101 odoo
5819; 0 0 TABLE ATTACH public sale_order_p20300101 odoo
Understanding TABLE ATTACH Entries
The TABLE ATTACH entries show that the backup includes a partitioned table. In PostgreSQL, a partitioned table is split into smaller tables known as partitions. These partitions help organize data effectively.
The parent table holds the overall structure. Each partition stores a part of the data, usually based on a rule, like a date range. When restoring PostgreSQL, it first rebuilds the parent table. Then, it uses the TABLE ATTACH command to link each partition to the parent table. This process brings back the partition setup and makes sure the partitioned table works just like it did before the backup.
6053; 2604 178842 DEFAULT public account_account id odoo
6052; 2604 178828 DEFAULT public account_account_tag id odoo
6092; 2604 179575 DEFAULT public account_accrued_orders_wizard id odoo
5989; 2604 176302 DEFAULT public account_analytic_account id odoo
5988; 2604 176290 DEFAULT public account_analytic_applicability id odoo
5991; 2604 176328 DEFAULT public account_analytic_distribution_model id odoo
5990; 2604 176314 DEFAULT public account_analytic_line id odoo
5987; 2604 176279 DEFAULT public account_analytic_plan id odoo
Understanding DEFAULT Entries
The DEFAULT entries set the default values for table columns. In these cases, they are used with id columns and tell PostgreSQL to automatically create a value when a new row is added.
This is usually done by using a sequence created for the table. When the restore process runs, PostgreSQL rebuilds these default expressions after creating the tables and sequences. This ensures new inserts still get values on their own. Without these default settings, people would need to enter values for columns, like id, every time they add a record.
10816; 0 178839 TABLE DATA public account_account odoo
10820; 0 178874 TABLE DATA public account_account_account_journal_rel odoo
10925; 0 179593 TABLE DATA public account_account_account_merge_wizard_rel odoo
10819; 0 178866 TABLE DATA public account_account_account_tag odoo
10818; 0 178858 TABLE DATA public account_account_res_company_rel odoo
10814; 0 178825 TABLE DATA public account_account_tag odoo
10856; 0 179137 TABLE DATA public account_account_tag_account_move_line_rel odoo
10838; 0 179006 TABLE DATA public account_account_tag_account_tax_repartition_line_rel odoo
10875; 0 179280 TABLE DATA public account_account_tag_product_template_rel odoo
10817; 0 178850 TABLE DATA public account_account_tax_default_rel odoo
11171; 0 239699 TABLE DATA public account_account_zson postgres
10922; 0 179572 TABLE DATA public account_accrued_orders_wizard odoo
Understanding TABLE DATA Entries
The table data entries show the rows that are saved in each table. While the table entries set up the table structure, the table data entries bring back the information that was in those tables.
When the restore happens, PostgreSQL puts the data into each table after the table structures are made. Each table data entry is for one table in the database; make sure all the old records come back.
16702; 0 0 SEQUENCE SET public account_account_id_seq odoo
16703; 0 0 SEQUENCE SET public account_account_tag_id_seq odoo
16704; 0 0 SEQUENCE SET public account_accrued_orders_wizard_id_seq odoo
16705; 0 0 SEQUENCE SET public account_analytic_account_id_seq odoo
16706; 0 0 SEQUENCE SET public account_analytic_applicability_id_seq odoo
16707; 0 0 SEQUENCE SET public account_analytic_distribution_model_id_seq odoo
16708; 0 0 SEQUENCE SET public account_analytic_line_id_seq odoo
16709; 0 0 SEQUENCE SET public account_analytic_plan_id_seq odoo
Understanding SEQUENCE SET Entries
The sequence set entries make sure the current value of each sequence is back to normal after the table data is loaded. Sequences are often used to create values for ID columns. If PostgreSQL only created the sequence again without setting its value, it would start giving out IDs from the beginning. This could cause problems, with key errors when new rows are added.
The sequence set entries stop this from happening by changing each sequence to the value based on the data that was already brought back. Because of this, any new records added after the restore will use the available sequence value instead of starting from the first one.
7355; 2606 178880 CONSTRAINT public account_account_account_journal_rel account_account_account_journal_rel_pkey odoo
7569; 2606 179599 CONSTRAINT public account_account_account_merge_wizard_rel account_account_account_merge_wizard_rel_pkey odoo
7352; 2606 178872 CONSTRAINT public account_account_account_tag account_account_account_tag_pkey odoo
7343; 2606 178849 CONSTRAINT public account_account account_account_pkey odoo
7349; 2606 178864 CONSTRAINT public account_account_res_company_rel account_account_res_company_rel_pkey odoo
7443; 2606 179143 CONSTRAINT public account_account_tag_account_move_line_rel account_account_tag_account_move_line_rel_pkey odoo
7388; 2606 179012 CONSTRAINT public account_account_tag_account_tax_repartition_line_rel account_account_tag_account_tax_repartition_line_rel_pkey odoo
Understanding CONSTRAINT Entries
The CONSTRAINT entries define the constraints that PostgreSQL applies to tables after restoring their data. In this section, the listed constraints are primary key constraints, which can be identified by names ending with _pkey.
A primary key ensures that every row in a table has a unique, non-null identifier. During the restore process, PostgreSQL first creates the tables and loads their data, then recreates these constraints to enforce data integrity. This order helps avoid conflicts while importing data. Once the constraints are restored, the postgres can prevent duplicate primary key values and maintain the uniqueness of each record in the database.
7339; 1259 179651 INDEX public account_account__account_type_index odoo
7340; 1259 179650 INDEX public account_account__code_store_index odoo
7341; 1259 179649 INDEX public account_account__name_index odoo
7353; 1259 178881 INDEX public account_account_account_journ_account_journal_id_account_ac_idx odoo
7567; 1259 179600 INDEX public account_account_account_merge_account_account_id_account_me_idx odoo
7350; 1259 178873 INDEX public account_account_account_tag_account_account_tag_id_account__idx odoo
7347; 1259 178865 INDEX public account_account_res_company_r_res_company_id_account_accoun_idx odoo
7441; 1259 179144 INDEX public account_account_tag_account_m_account_account_tag_id_accoun_idx odoo
Understanding INDEX Entries
The INDEX entries represent the indexes created for database tables. An index is a separate database object that helps PostgreSQL locate rows more efficiently without scanning the entire table. During the restore process, PostgreSQL recreates these indexes after loading the table data.
Creating indexes after the data has been restored makes the restore process faster because PostgreSQL does not need to update the indexes for every inserted row. Once the indexes are recreated, they improve the performance of queries that search, filter, sort, or join data.
8374; 0 0 INDEX ATTACH public sale_order_default_campaign_id_idx odoo
8375; 0 0 INDEX ATTACH public sale_order_default_company_id_idx odoo
8376; 0 0 INDEX ATTACH public sale_order_default_create_date_idx odoo
8377; 0 0 INDEX ATTACH public sale_order_default_date_order_id_idx odoo
8378; 0 0 INDEX ATTACH public sale_order_default_medium_id_idx odoo
8379; 0 0 INDEX ATTACH public sale_order_default_name_idx odoo
8380; 0 0 INDEX ATTACH public sale_order_default_partner_id_idx odoo
8381; 0 0 INDEX ATTACH public sale_order_default_partner_invoice_id_idx odoo
8382; 0 0 INDEX ATTACH public sale_order_default_partner_shipping_id_idx odoo
Understanding INDEX ATTACH Entries
The INDEX ATTACH entries are related to partitioned tables. Unlike regular indexes, these entries do not create new indexes. Instead, they attach indexes that belong to individual partitions to the corresponding index on the parent partitioned table. During the restore process, PostgreSQL first recreates the indexes for each partition and then uses the INDEX ATTACH entries to rebuild the relationship between the parent index and its partition indexes.
This ensures that the partitioned table retains the same index hierarchy as it had before the backup was created.
9379; 2606 179952 FK CONSTRAINT public account_account_account_journal_rel account_account_account_journal_rel_account_account_id_fkey odoo
9380; 2606 179957 FK CONSTRAINT public account_account_account_journal_rel account_account_account_journal_rel_account_journal_id_fkey odoo
9635; 2606 181357 FK CONSTRAINT public account_account_account_merge_wizard_rel account_account_account_merge_wiza_account_merge_wizard_id_fkey odoo
9636; 2606 181362 FK CONSTRAINT public account_account_account_merge_wizard_rel account_account_account_merge_wizard_re_account_account_id_fkey odoo
9377; 2606 179942 FK CONSTRAINT public account_account_account_tag account_account_account_tag_account_account_id_fkey odoo
9378; 2606 179947 FK CONSTRAINT public account_account_account_tag account_account_account_tag_account_account_tag_id_fkey odoo
Understanding FK CONSTRAINT Entries
The FK CONSTRAINT entries represent foreign key constraints defined in the database. A foreign key creates a relationship between two tables by ensuring that values in one table correspond to valid values in another table.
During the restore process, PostgreSQL recreates these constraints after restoring the tables, data, primary keys, and indexes. This order prevents errors that could occur if a foreign key references data that has not yet been restored. Once these constraints are recreated, PostgreSQL enforces the relationships between tables and helps maintain referential integrity by preventing invalid references.
Now, extract the .tar file using the tar command.
mkdir backup_contents
tar -xf /tmp/odoo_backup.tar -C backup_contents
Now, check the contents inside the folder where we did the extraction.
ls backup_contents/
Result:
10307.dat 10364.dat 10413.dat 10466.dat 10518.dat 10571.dat 10619.dat 10668.dat 10719.dat 10770.dat 10817.dat 10862.dat 10909.dat 10954.dat 10993.dat 11039.dat 11088.dat 11156.dat
10308.dat 10365.dat 10415.dat 10468.dat 10520.dat 10572.dat 10620.dat 10670.dat 10721.dat 10772.dat 10818.dat 10864.dat 10911.dat 10956.dat 10995.dat 11041.dat 11090.dat 11158.dat
10309.dat 10367.dat 10417.dat 10470.dat 10522.dat 10574.dat 10622.dat 10672.dat 10722.dat 10774.dat 10819.dat 10866.dat 10913.dat 10957.dat 10996.dat 11043.dat 11091.dat 11160.dat
10310.dat 10369.dat 10419.dat 10472.dat 10524.dat 10576.dat 10623.dat 10674.dat 10724.dat 10776.dat 10820.dat 10868.dat 10914.dat 10959.dat 10998.dat 11045.dat 11093.dat 11162.dat
10311.dat 10370.dat 10421.dat 10474.dat 10526.dat 10578.dat 10625.dat 10676.dat 10726.dat 10778.dat 10822.dat 10869.dat 10916.dat 10961.dat 10999.dat 11047.dat 11095.dat 11164.dat
10338.dat 10387.dat 10441.dat 10493.dat 10546.dat 10597.dat 10644.dat 10693.dat 10355.dat 10404.dat 10456.dat 10511.dat 10563.dat 10611.dat 10658.dat 10710.dat 10761.dat 10808.dat 10854.dat 10901.dat 10947.dat 10988.dat 11030.dat 11080.dat 11150.dat
10357.dat 10406.dat 10458.dat 10512.dat 10565.dat 10613.dat 10660.dat 10711.dat 10763.dat 10810.dat 10855.dat 10902.dat 10948.dat 10989.dat 11032.dat 11082.dat 11152.dat
10359.dat 10407.dat 10460.dat 10514.dat 10567.dat 10614.dat 10662.dat 10713.dat 10765.dat 10812.dat 10856.dat 10904.dat 10949.dat 10990.dat 11034.dat 11083.dat 11153.dat
10360.dat 10409.dat 10462.dat 10516.dat 10568.dat 10615.dat 10664.dat 10715.dat 10767.dat 10814.dat 10858.dat 10905.dat 10951.dat 10991.dat 11036.dat 11085.dat 11154.dat
10362.dat 10411.dat 10464.dat 10517.dat 10569.dat 10617.dat 10666.dat 10717.dat 10769.dat 10816.dat 10860.dat 10907.dat 10952.dat 10992.dat 11038.dat 11086.dat 11155.dat
When we open one of the .dat files, it looks like this
Understanding the .dat Files
After extracting the TAR backup, you will find many files with the .dat extension. These files store the actual data associated with the objects listed in the backup archive. The file name itself is a numeric identifier that Postgres uses internally to map the file to a specific database object.
For example, examining the contents of 5806.dat using the following command:
cat /home/cybrosys/backup_contents/5806.dat
produces output similar to:
public.sale_order create_date \N \N 1 year range 4 on public.template_public_sale_order \N \N t t none \N 30 f YYYYMMDD tf f f t t \N \N f \N \N
\.
This output shows that the file contains data for the sale_order table. Each line represents a row, with individual column values separated by tabs. The special value \N indicates a NULL value, meaning no data is stored for that column. The final line, \., marks the end of the data in the file. During the restore process, PostgreSQL reads these .dat files and loads their contents into the corresponding tables, recreating the database records exactly as they existed when the backup was taken.
At the end of the contents inside the extracted folder, we can see the two files named
Open the restore.sql file.
cat restore.sql
Result:
Understanding the restore.sql File
When a PostgreSQL TAR backup is extracted, we can see a file named restore.sql. This file contains the SQL commands that PostgreSQL uses to recreate the database objects during a restore.
Instead of storing table data, it stores statements for creating database objects such as tables, sequences, indexes, constraints, triggers, and other schema definitions. The actual table data remains in the .dat files, while restore.sql provides the SQL structure needed to rebuild the database schema.
This file is mainly intended for internal use by pg_restore during the restoration process, but it can also be inspected to understand the order in which database objects are recreated and to review the SQL definitions generated from the backup.
Now, check the other file named toc.dat ( table of contents )
cat toc.dat
Result:
Understanding the toc.dat File
The toc.dat file is one of the important files in a PostgreSQL TAR backup. TOC stands for Table of Contents. This file does not hold the actual table data. Instead, it stores metadata about every object that's part of the backup. This includes tables, sequences, indexes, constraints, functions, and comments. It also keeps track of the order in which these objects should be restored.
When pg_restore is used to restore a TAR backup, it reads the toc.dat file first. This helps it know which objects must be recreated. In what sequence? This order is very important. It makes sure that objects that depend on others, like indexes or foreign key constraints, are restored after the tables they rely on are already in place.
Even though the toc.dat file is mainly meant for use by pg_restore, it can also be checked. Doing this can help you see the structure of the backup. It allows you to verify which database objects are included in the backup.
A PostgreSQL TAR backup is more than one archive file. It contains all the pieces needed to rebuild a database. This includes the schema, table data, indexes, sequences, constraints, and other metadata. You can explore the backup using tools like tar and pg_restore. You can also look at files like.dat, restore.sql, and toc.dat to see how PostgreSQL organizes backups.
Looking inside a TAR backup is a way to check what’s inside before you restore it. It lets you confirm that all the needed database objects are present. It also helps you understand how the restore process works. It makes it easier to find and fix problems with backups.
Whether you are learning about PostgreSQL internals or working with production databases, knowing how to analyze a TAR backup gives you confidence. It helps you make sure your backups are reliable when you need them most.