This is because it is the way pg_restore works.
pg_restore manual reads as follow :
-C, --create
Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before
connecting to it.
When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE
commands. All data is restored into the database name that appears in the archive.
The -d will restore in the given database if and only if -C is not used. If -C is used, the database is used as a "launchpad", not as the destination.
If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.
Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)
sudo service postgresql start
Translates to something like:
sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start
That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:
sudo -u postgres pg_ctlcluster 9.3 main start
To get the status of all currently installed db clusters use pg_lsclusters:
sudo -u postgres pg_lsclusters
You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:
data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/
You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with
data_directory =
The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?
Or are you running a non-default installation?
Best Answer
It came out that there was a stupid hack, unaccent function was marked immutable and there was an index that used this unaccent function. Dropping the index and recreating it after backup did the trick.
Do not use hacks, folks :)