卸载PostgreSQL 14后PGAdmin中PostgreSQL 13数据库丢失，数据是否无法恢复？

Hey there, let’s break this down step by step to figure out what went wrong and how you can get your spatial data back.

First off, your PostgreSQL 13 data is almost certainly not actually lost—this is almost always a configuration mix-up from the PostgreSQL 14 uninstall process:

• When you installed PostgreSQL 14, it probably modified system-level settings like environment variables (PGDATA), registry entries (on Windows), or service configurations. This might have shifted your default connection to an empty new data directory instead of your original 13 data folder.
• The uninstaller for 14 could have accidentally removed the service entry for PostgreSQL 13, or overwritten global config files like pg_hba.conf or postgresql.conf, making your 13 instance unstartable or unaccessible.
• A less likely scenario: During the 14 installation, you might have accidentally selected your 13 data directory as the default for 14 (even if you never used 14). When you uninstalled 14, the tool might have flagged that directory for deletion—but you manually kept it, so the data’s still there.

Great news: Since you still have both the 13 and 14 data folders, your chances of recovering all your spatial tables and data are extremely high. The key is figuring out which folder actually holds your original data, then re-establishing access to it.

Step 1: Locate your actual data #

First, check both data folders to see where your tables live:

1. Navigate to the base subfolder in both your PostgreSQL 13 and 14 data directories. Inside base, you’ll see numbered subfolders (each corresponds to a database).
2. Look for the PG_VERSION file in the root of each data directory: The 13 folder should have 13 inside, the 14 folder 14.
3. Dig into the numbered subfolders in base—if you see files ending in .heap, .index, or other PostgreSQL table-related extensions (and recognize the volume of files matching your old database), that’s the data folder you need.

Step 2: Try starting your original PostgreSQL 13 instance #

If you confirm your data is in the 13 data folder:

• Windows:
  1. Open an elevated Command Prompt, then navigate to your 13 bin directory (e.g., cd "C:\Program Files\PostgreSQL\13\bin").
  2. Start the instance manually by pointing to your 13 data folder:

     pg_ctl -D "C:\Program Files\PostgreSQL\13\data" start
     

  3. If it starts successfully, fire up PGAdmin 4 or QGIS and connect to localhost:5432—your old tables should be there.
  4. If you get an error, check postgresql.conf for port conflicts, or pg_hba.conf to make sure your connection is allowed.
• Linux/macOS:
  1. Open a terminal and go to your 13 bin directory (e.g., cd /usr/local/pgsql/13/bin).
  2. Start the instance with:

     ./pg_ctl -D /var/lib/postgresql/13/data start
     

     (Adjust the data path to match your system.)

Step 3: If your data is in the PostgreSQL 14 data folder #

If you find your original tables in the 14 data folder (this would mean the 14 installation migrated your 13 data automatically):

1. Reinstall PostgreSQL 14 temporarily (no need to uninstall 13) to get the 14 binaries working.
2. Start the 14 instance, then use pg_dump to export your spatial data:
   • To export an entire database:

     pg_dump -h localhost -p 5432 -U your_username -d your_spatial_db -F c -b -v -f /path/to/your_backup.dump
     

   • To export a single spatial table:

     pg_dump -h localhost -p 5432 -U your_username -d your_spatial_db -t your_spatial_table -F c -b -v -f /path/to/table_backup.dump
     

3. Stop the 14 instance, start your 13 instance, then use pg_restore to import the backup into 13:

   pg_restore -h localhost -p 5432 -U your_username -d your_spatial_db -v /path/to/your_backup.dump
   

   Note: Make sure PostGIS is installed on your 13 instance before importing spatial tables—otherwise you’ll get errors.

Step 4: Fix permanent service access (optional) #

If you can start the 13 instance manually but want it to run automatically:

• Windows: In the 13 bin directory, run:

  pg_ctl register -N "postgresql-13" -D "C:\Program Files\PostgreSQL\13\data"
  

• Linux: Create a systemd service file for PostgreSQL 13 (e.g., /etc/systemd/system/postgresql-13.service) with the correct path to your 13 postgres binary and data directory, then enable it with systemctl enable postgresql-13.

Critical Notes #

• Backup first! Before doing anything, make a full copy of both your 13 and 14 data folders to avoid accidental data loss.
• PostgreSQL data directories are version-locked—never try to start a 13 instance with a 14 data folder, or vice versa. This will corrupt your data.
• If you run into permission errors, make sure the PostgreSQL service user has access to the data directory.

内容的提问来源于stack exchange，提问作者jeromeon