Restoring database from false update

From: Maksim Fomin <maxim(at)fomin(dot)one>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Restoring database from false update
Date: 2020-11-13 20:56:38
Message-ID: d-dSPyz7AbptW8-tvu3Q476dFdcnnBdg4YEaJwwoHf9osuhxJBoF_-mDtrwJ_EVqGHl43wqfP-QAN8cNvXpIatC1dAJe5wcQaqYvnUq1VbA=@fomin.one
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

Yesterday I updated the version of posgtresql from 12.0 to 12.5-1. Today I found that for some reason (*) there was bug in package update script which caused the postgresql to think that it should be updated from 12 to 13. I followed instructions to update postgresql (**).
What I did:
1) copied postgresql folder to backup destination (mv)
2) created new directories (mkdir) and changed permissions (chown)
3) executed 'initdb' inside new folder

Then I tried to upgrade the database via 'pg_upgrade' but realised that I cannot do that because in my distro binaries of previous package version are gone, so I cannot supply arguement to the '-b' parameter. At this point I realized that the upgrade step should not be necessary for transition from 12 to 12.5. I found the issue in bugzilla (*) and updated postgresql from 12.5-1 12.5-3. Later, I stopped the service and moved backup folder to the usual place. For some reason psql shows that there are no relations found in the database, although the database is listed. My next step was to copy data from file-system level backup (about 4-5 days ago) but the result was the same.

How I can restore the database?

* https://bugs.archlinux.org/task/68601
** https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2020-11-13 20:58:04 Re: Range partitioning and overlap
Previous Message Adrian Klaver 2020-11-13 20:48:20 Re: New "function tables" in V13 documentation