Database crashes are every production engineer’s nightmare. Whether the crash is caused by disk issues, abrupt shutdowns, kernel panic, or misconfiguration, the real panic starts when backups are missing or outdated. In such situations, the PostgreSQL data directory becomes the most valuable asset.
This blog explains, step by step, how PostgreSQL can be brought back to life using only the existing data directory, without logical backups. The walkthrough is based on a real local experiment that closely mirrors a production recovery scenario.
All commands, outputs, and examples used here are from an actual test environment.
Understanding the PostgreSQL Data Directory
The PostgreSQL data directory contains everything required to reconstruct a database cluster:
- System catalogs (pg_class, pg_database, pg_attribute, etc.)
- User data files (heap files, indexes, TOAST data)
- WAL files (Write-Ahead Logs)
- Configuration state related to the cluster
As long as this directory is intact and consistent, PostgreSQL can usually recover, even if the original cluster configuration is lost.
This experiment demonstrates exactly that.
Creating a Fresh PostgreSQL Cluster
A new PostgreSQL 17 cluster named odoo_production was created using pg_createcluster.
sudo pg_createcluster 17 odoo_production
Output:
Creating new PostgreSQL cluster 17/odoo_production ...
/usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/odoo_production --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
The database cluster will be initialized with locale "en_IN".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Cluster status after creation:
Ver Cluster Port Status Owner Data directory Log file
17 odoo_production 5436 down postgres /var/lib/postgresql/17/odoo_production /var/log/postgresql/postgresql-17-odoo_production.log
Starting the Cluster and Verifying PostgreSQL
The cluster was started manually:
sudo systemctl start postgresql@17-odoo_production.service
Connected to PostgreSQL:
sudo su postgres
psql -p 5436
PostgreSQL version confirmation:
select version();
PostgreSQL 17.7 (Ubuntu 17.7-3.pgdg22.04+1) on x86_64-pc-linux-gnu
Existing databases:
\l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_IN | en_IN | | |
template0 | postgres | UTF8 | libc | en_IN | en_IN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_IN | en_IN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
Data directory verification:
show data_directory;
You get result like this
/var/lib/postgresql/17/odoo_production
Creating Sample Data
A test database and table were created to validate recovery behavior.
Create Database
create database odoo_db;
Connect to the database:
\c odoo_db
Create Table
CREATE TABLE sales_order (
id SERIAL PRIMARY KEY,
order_number VARCHAR(20) UNIQUE NOT NULL,
customer_name VARCHAR(100) NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
Insert Sample Records
INSERT INTO sales_order (order_number, customer_name, order_date, total_amount, status)
VALUES
('SO-1001', 'Marc demo', '2026-02-01', 12500.50, 'confirmed'),
('SO-1002', 'Paul Allen', '2026-02-02', 8200.00, 'draft'),
('SO-1003', 'Jhon peter', '2026-02-03', 15499.99, 'confirmed'),
('SO-1004', 'Stephen', '2026-02-05', 4300.75, 'cancelled');
Simulating a Crash Scenario
The odoo_production cluster was stopped intentionally:
sudo systemctl stop postgresql@17-odoo_production.service
Cluster status after stopping:
pg_lsclusters
It shows result like this
Ver Cluster Port Status Owner Data directory
17 odoo_production 5436 down postgres /var/lib/postgresql/17/odoo_production
At this point, assume the original cluster is unusable, but the data directory is still intact.
Creating a New Cluster for Recovery
A new cluster named recovery_test was created
sudo pg_createcluster 17 recovery_test
Verify the status of this newly created cluster by using the command pg_lsclusters
Ver Cluster Port Status Owner Data directory
17 recovery_test 5437 down postgres /var/lib/postgresql/17/recovery_test
Reusing the Old Data Directory
The key recovery step is modifying the new cluster to point to the old data directory.
Edit the newly created clusters postgres configuration file:
sudo nano /etc/postgresql/17/recovery_test/postgresql.conf
Inside the file ,find the parameter named data_directory and change the path of the data directory to the old data directory of postgres
data_directory = '/var/lib/postgresql/17/odoo_production'
Then try to start the recovery cluster like this and check it is running
sudo systemctl start postgresql@17-recovery_test.service
Verifying Recovery Success
Cluster status:
pg_lsclusters
You should see that the recovery cluster is running successfully
postgres@cybrosys:/home/cybrosys$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
17 recovery_test 5437 online postgres /var/lib/postgresql/17/odoo_production /var/log/postgresql/postgresql-17-recovery_test.log
Connect to PostgreSQL:
sudo su postgres
psql -p 5437
List databases and check the database is exist correctly
\l
Result :
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
odoo_db | postgres | UTF8 | libc | en_IN | en_IN | | |
postgres | postgres | UTF8 | libc | en_IN | en_IN | | |
template0 | postgres | UTF8 | libc | en_IN | en_IN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_IN | en_IN | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
Connect to recovered database:
\c odoo_db
List tables:
\d
Result :
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | sales_order | table | postgres
public | sales_order_id_seq | sequence | postgres
(2 rows)
Query recovered data:
select * from sales_order;
id | order_number | customer_name | order_date | total_amount | status
----+--------------+---------------+------------+--------------+-----------
1 | SO-1001 | Marc demo | 2026-02-01 | 12500.50 | confirmed
2 | SO-1002 | Paul Allen | 2026-02-02 | 8200.00 | draft
3 | SO-1003 | Jhon peter | 2026-02-03 | 15499.99 | confirmed
4 | SO-1004 | Stephen | 2026-02-05 | 4300.75 | cancelled
All data was recovered successfully.
Understanding pg_lsclusters
The pg_lsclusters utility helps manage multiple PostgreSQL clusters on Debian/Ubuntu systems.
pg_lsclusters --help
Result :
Usage: /usr/bin/pg_lsclusters [-hjs]
-h --no-header Omit column headers in output
-j --json JSON output
-s --start-conf Include start.conf information
This tool was essential to track cluster states during recovery.
Why This Recovery Method Works
PostgreSQL stores logical structure and physical data together in the data directory. When a new cluster points to an existing, valid data directory:
- System catalogs are loaded normally
- WAL replay ensures crash consistency
- Tables and indexes become immediately accessible
This is not a hack, but a supported recovery behavior when metadata and binaries are compatible.
Limitations and Warnings
- PostgreSQL version must be compatible
- File ownership and permissions must be correct
- Corrupt data directories cannot always be recovered
- This does not replace proper backups
This experiment proves an important real-world lesson:
If you still have the PostgreSQL data directory, your data is usually not lost.
While backups and replication are essential, understanding how PostgreSQL clusters work internally can turn a catastrophic crash into a recoverable incident.
This recovery approach is especially valuable in emergency production scenarios where time matters and backups are unavailable.