# PostgreSQL 8.0 migration fails. How to do it right?

## hurricane

(For a lack of a better forum, and because I could not find anything about it on the net, I’m posting this here.)

I just now wanted to migrate my hardened server to postgresql 8(.1.11). But the ebuild told me to dump the databases, and restore them later.

So I dumped them with

```
pg_dumpall > dump.sql
```

moved the contents of /var/lib/postgresql to a backup dir, and updated postgresql.

Problem is: I can’t re-import anything anymore. Trying

```
pg_restore < dump.sql
```

only gives me

```
pg_restore: [Archivierer] Eingabedatei scheint kein gültiges Archiv zu sein
```

 (illegal input file)

So I tried psql. But as there seems to be no database in postgres yet, I just get this:

```
# psql < dump.sql

psql: FATAL:  Datenbank »root« existiert nicht
```

 (Database does not exist)

Well, I think I knew that. But let‘s see what databases are existing:

```
# psql -l

psql: FATAL:  Datenbank »postgres« existiert nicht

DETAIL:  Das Datenbankunterverzeichnis »/var/lib/postgresql/data/pg_tblspc/499/10793« fehlt.
```

 (Database postgres does not exist too. And the database subdir is missing too.)

Great. Just great. For something as important as a database, this “migration path” is fucked up beyond all recognition (FUBAR).

Does anyone know how to fix this? I mean importing the stuff without there being a database (I guess).

Whoever is the maintainer of this package: You can come collect your wreath of failure. But you are free to give it to the PostrgreSQL team, in case they do really not offer a clean automated migration procedure. (I could not find one.)

I say, if you got no fully-automated migration for such an important server package, then you are not done, and have to to it, before releasing it.

Meanwhile, I will mask that trash, and downgrade back.

On days like these, I wish there would be someone to sue in a company that got my money, for creating a non-fit product. But this is the downside of open source. You did not pay, you have nothing to say. I can only walk away and tell people.

----------

## gnu11111

Same sh*t here... no warning, no ebuild-comment, no migration documentation, no whatsoever. Database initialization with "emerge postgresql --config" simply doesn't work and "/var/lib/postgresql/data/pg_tblspc/" is empty, when it's finished. The only solution seems to be the downgrade to version 8.0  :Confused: 

----------

## kashani

Dump and restore is pretty standard within Postgres at least since I've used it starting with 7.1. Occasionally you don't need to do it if you stay within the minor revision, 8.1.1 to 8.1.2, but it's usually safer. According to the docs for 8.1.11 it's required if you're coming from 8.0.

http://www.postgresql.org/docs/8.1/static/release-8-1-11.html

kashani

----------

## hurricane

It might be standard (for whatever reason that is considered to somehow make it OK, is beyond me  :Wink: ), but there is no information anywhere (especially not in what you linked to, kashani ^^), on how to actually restore the data.

Well, you can restore the compressed archive formats. But the plain-text-format can’t be imported with that tool.

So what I searched for, is how to restore such data.

I looked into it, and it is plain SQL, from what I can see.

But just executing it with psql, as seen, does not work.   :Confused: 

Does anyone know, how to do that?

----------

## scherz0

Disclaimer : portage does not fit my needs for postgresql.  So I just don't use it (and I don't blame anyone for that).

 *hurricane wrote:*   

> But just executing it with psql, as seen, does not work.  

 

Using psql is exactly the way to do it.  See 23.4. Migration Between Releases. See also Chapter 23. Backup and Restore for more information on backup and restore.

For some reason, your database was not initialized during the upgrade.

 *Quote:*   

> Does anyone know, how to do that?

 

ebuild config should do the initialization.  If not, you could try to unmerge, then emerge postgres.

----------

## gnu11111

The PostgreSQL-distribution and their migration-path works perfectly well. Hurricane blamed the gentoo-ebuild, where the database-initialization doesn't work at all. So the whole ebuild is pretty useless as it doesn't provide any working database. This is just a warning to other users not to use "emerge" for either installing or upgrading to postgres-8.1.

----------

## doesntcount

After upgrading to 8.1.11, I'm having the same issue.

```
walter / # su - postgres

postgres@walter ~ $ psql

psql: FATAL:  database "postgres" does not exist

DETAIL:  The database subdirectory "/var/lib/postgresql/data/pg_tblspc/499/10793" is missing.

postgres@walter ~ $ 

```

This was after initializing the database with emerge --config =postgresql-8.1.11.

Seems to be a pretty serious issue. Why isn't postgres 8.1.11 masked until this is resolved?

----------

## twork

 *hurricane wrote:*   

> 
> 
> So I dumped them with
> 
> ```
> ...

 

I just ran into this myself and had to re-educate myself on how a Postgres database is restored.  Steps which appear to have worked for me:

Dump the db using pg_dumpall, like you did;

Stop postgresql, set aside the data directory, update the package;

```
emerge postgresql --config
```

 ...creates a new blank database;

Start the database server;

```
man pg_dumpall
```

 :) ...which includes the example, 

```
$ psql -f db.out postgres
```

 ...which is misleading if you run your psql command as any user other than the postgres user, since it will complain that the database named for that user doesn't exist. :( But it also says it doesn't matter which database you connect to for a complete restore, since everything will be completely rebuilt.

The command line which ended up working for me was: 

```
 # psql -f /path/to/dumpfile postgres postgres
```

----------

## Joseph_sys

 *hurricane wrote:*   

> [snip]
> 
> Problem is: I can’t re-import anything anymore. Trying
> 
> ```
> ...

 

Try to follow these instructions:

http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html

specifically when restoring use:

```
psql -d postgres -f dump.sql
```

It worked on my system, however it was a new installation.

----------

## ThePsychoHobbit

I'm not sure if you're still having this issue or not.  I finally got around to upgrading today and ran into the same issue.  I took what, for me, was a perfectly normal upgrade path usually (both in the past, as well as on our CentOS clusters at work) -- a process virtually identical to yours and everyone else's.  Afterward I found that there was a lingering postmaster process, despite the `/etc/init.d/postgresql stop`.  Killing that process, removing (again) /var/lib/postgresql (as created by the bad `emerge --config`) and picking back up with `emerge --config postgresql` fixed the problem for me.  Hopefully this helps and your problem is as simple, mundane, and quick to fix.

----------

