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