Re: Migrating database

Karoly Guba wrote:
> In my company there is a production database server (postgresql v. 9.1.24lts.2). Since this server is running
> out of space soon, we decided to migrate the database over to a new server with increased hardware and more space.
> In the new server we have Debian 9.3 and postgresql 9.6. The database size is almost 800GB mainly text/number fields,
> but we have no backup at all.
>
> I thought it will be an easy process, just dump over the db to the new server, but the pg_dump failed trowing this message:
> "pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: missing chunk number 1 for toast value 7043981 in pg_toast_16498
> pg_dump: The command was: COPY public.article_classified_zh (id, author, ...) TO stdout;".
>
> As a next step I turned off the live database and copied the data files (old server /var/lib/postgresql/9.1/main)
> over to the new server ( new server /var/lib/postgresql/9.6/main ). After the rsync has finished, I turned on
> the live db, and it started properly. As a next step I started the db on the new server, but it doesn't work.
>
> I am not a postgresql expert, but it looks like I have a data corruption. The problem is that I have no backup at all.
> Question:
>
> Can you please suggest me a way of how to migrate over the database in this situation? Downtime is not problem.

You cannot simply start a 9.1 database with 9.6, you have to run pg_upgrade
or dump/restore the database.

I think you should deal with the data dorruption first.

First step: shut down the database and take an offline backup of the
data directory and all other files (tablespaces?) that belong to the
database.

The first attempt would be
REINDEX TABLE public.article_classified_zh;
Perhaps it is just a corrupted TOAST index.

If not:
You will have to identify which rows in "article_classified_zh" are affected,
i.e. which throw such an error when you try to query them by primary key.
Then delete all these rows and try pg_dump again.
These deleted rows are lost.

You must run 9.6 pg_dump if you want to upgrade the database.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com