Migrating database

From: Karoly Guba <guba(dot)karoly(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Migrating database
Date: 2018-02-25 19:38:35
Message-ID: CAGrYjcxOaJHGPN=ALpZh6+V9BPH=3WxRA_N8pH+5r9K12Gzo4Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Dear all,

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.

Is it possible to start the new server with the rsync-ed files?

Thanks a lot,
Karoly

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Piotr Włodarczyk 2018-02-25 20:07:09 Re: Migrating database
Previous Message David G. Johnston 2018-02-25 05:34:24 Re: how do i change the password for 'postgres' user