Backing up a postgres database

The postgres documentation is great, you can find pg_dump here: http://www.postgresql.org/docs/8.3/static/app-pgdump.html. To backup a database, you can simply redirect the output of pg_dump to a file.

$ pg_dump databasename > out.sql

Use git, mercurial scp, sftp or another way to get this file to the new machine. If all of the roles that were involved in building of the original database are identical and the template that you created both databases with is the same, everything is fine and you can run the following command on the new machine and have a nice day:

$ psql databasename < out.sql

But, if the user that created the database is different than the owner of the new database or there is some other error, you may be able to get away with opening the file with vim and running as simple find and replace rather than setting up a new user, editing your postgres permissions, restarting the database server. These options might not be good for a variety of reasons. Fortunately, our out.sql is output in an incredibly readable, even self-documenting form.

You will see the pertinent ownership information in plain SQL with plenty of whitespace.

$ vim out.sql

If you don't already have syntax highlighting you can turn it on by hitting escape to make sure that you are in command mode and then:

:syn on

Now you see different blocks of code like:

--
-- Name: geometry_dump; Type: TYPE; Schema: public; Owner: skyl
--

CREATE TYPE geometry_dump AS (
        path integer[],
        geom geometry
);


ALTER TYPE public.geometry_dump OWNER TO skyl;

Here, in machine1, I had a user named skyl who created the database and delegated permissions to db_user.

local   all              skyl                         ident sameuser
local   databasename     db_user                          md5

with a command like:

(env)skyl@machine1:~$ createdb -T t_postgis -O db_user databasename

or something like:

(env)skyl@machine1:~$ psql template1

=> CREATE DATABASE databasename OWNER db_user ENCODING 'UTF8';

Perhaps in the second machine you created the database with a user by a different name, other_user. You can open the sql file with vim and issue the command:

:%s/skyl/other_user/g