Questions[database](server)

Is there a way to export a PostgreSQL database and later import it with another name?

I'm using PostgreSQL with Rails and I often export the data from production, where the database is called blah_production and import it on development or staging with names blah_development and blah_staging. On MySQL this is trivial as the export doesn't have the database anywhere (except a comment maybe), but on PostgreSQL it seems to be impossible. Is it impossible?

I'm currently dumping the database this way:

pg_dump blah > blah.dump

I'm not using the -c or -C options. That dump contains statements such as:

COMMENT ON DATABASE blah IS 'blah';

ALTER TABLE public.checks OWNER TO blah;

ALTER TABLE public.users OWNER TO blah;

When I try to import with

psql blah_devel < blah.dump

I get

WARNING:  database "blah" does not exist

ERROR:  role "blah" does not exist

Maybe the problem is not really the database but the role?

If I dump it this way:

pg_dump --format=c blah > blah.dump

and try to import it this way:

pg_restore -d blah_devel < tmp/blah.psql

I get these errors:

pg_restore: WARNING:  database "blah" does not exist
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1513; 1259 16435 TABLE checks blah
pg_restore: [archiver (db)] could not execute query: ERROR:  role "blah" does not exist
    Command was: ALTER TABLE public.checks OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1509; 1259 16409 TABLE users blah
pg_restore: [archiver (db)] could not execute query: ERROR:  role "blah" does not exist
    Command was: ALTER TABLE public.users OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1508; 1259 16407 SEQUENCE users_id_seq blah
pg_restore: [archiver (db)] could not execute query: ERROR:  role "blah" does not exist
    Command was: ALTER TABLE public.users_id_seq OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1824; 0 0 ACL public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  role "postgres" does not exist
    Command was: REVOKE ALL ON SCHEMA public FROM postgres;
pg_restore: [archiver (db)] could not execute query: ERROR:  role "postgres" does not exist
    Command was: GRANT ALL ON SCHEMA public TO postgres;
WARNING: errors ignored on restore: 11

Any ideas?

I've seen out there some people using sed scripts to modify the dump. I'd like to avoid that solution but if there are no alternative I'll take it. Has anybody wrote a script to alter the dump's database name ensure no data is ever altered?

I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing:

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'bob'@'10.1.1.1' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT, UPDATE, DELETE on MyDatabse.* to 'bob'@'localhost', 'bob'@'10.1.1.1';

This works fine, but is there any more elegant way to create a user account that is linked to multiple IPs or does it need to be done this way?

My main worry is that in the future, permissions will be updated for one 'bob' account but not the other.

I'm trying to import a gzipped SQL file into mysql directly. Is this the right way?

mysql -uroot -ppassword mydb > myfile.sql.gz

I tried to look for benchmark on the performances of various filesystems with MySQL InnoDB but couldn't find any.

My database workload is the typical web-based OLTP, about 90% read, 10% write. Random IO.

Among popular filesystems such as ext3, ext4, xfs, jfs, Reiserfs, Reiser4, etc. which one do you think is the best for MySQL?

There are many SQL Server options that can be enabled for databases, and one of the most misunderstood ones is auto-shrink. Is it safe? If not, why not?