Postgresql – Cannot rename database (the user in –username is the owner)

postgresqlpsql

I connect with psql and list my databases:

                                          List of databases
           Name            |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------------------+----------+----------+-------------+-------------+-----------------------
 accyem_20150422           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 accyem_template           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 clinicama_template        | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 intercontinental_template | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 pijalarga                 | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 postgres                  | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 sbu_template              | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 sf_20150505               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 sf_20150527               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 sf_template               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 tarpuq_template           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | 
 template0                 | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | =c/postgres          +
                           |          |          |             |             | postgres=CTc/postgres
 template1                 | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | =c/postgres          +
                           |          |          |             |             | postgres=CTc/postgres
 yucha                     | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |

I am interested on sf_template database.

I want to run the following command:

PGPASSWORD=mypassword  psql --username=openerp --dbname=sf_template --no-password --command="alter database sf_template rename to sf_template_20150605111128"

But get the following error:

ERROR: current database cannot be renamed

As I see in the returned results, the user I log in with is the owner of the database. Why can't I rename the database?

Best Answer

You cannot rename a database you're connected to. I did not know that point or even considered it.

Related Solutions

Postgresql – Connect to PgAdmin III via localhost

The basic misunderstanding: "localhost" is not a local connection. Your "trust" line in pg_hba.conf is not applicable for your connection:

local   all   all   trust

That only applies for connections via Unix-domain socket. Best choice for you is to connect via this route. Per pgAdmin documentation:

The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.

Bold emphasis mine.

More about connecting without password in this related answer on SO:
Run batch file with psql command without password

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Best Answer

Related Solutions

Postgresql – Connect to PgAdmin III via localhost

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

Related Question