[mb-devel] HOWTO - Creating just the database in a VMware box with Debian

[Dave Evans]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Arcade wrote:
> Thanks goes to Dave for helping me with this.  It took me a while, but I
> finally got it!  I'm posting my HOWTO for anyone who wants to do it
> themselves ....

Excellent job!

If you just want the data in a database, and you don't want Apache etc,
here's another method.

* It's more flexible (you can choose not to bother creating the foreign
key constraints, for example)
* It has fewer prerequisites (you don't need Perl, nor any of the Perl
modules, nor postgresql-devel)
* However it is a bit more "hands on" - you have to check each step for
errors yourself, and the import is about 10 or so commands instead of
just one.

Here's the method:

Install the base system.  Install postgresql (you need both the server
and the client, if your system provides them separately).  Install bzip2
and cvs.

Check mb_server out of CVS, and download mbdump*.tar.bz2, making sure
they match.  Configure postgresql the way you want it.

(Here mbrainz is the UNIX user I'm running as; musicbrainz_user is the
postgresql user I connect as; importtest is the database I'm setting up).

Create the empty database:
mbrainz$ createdb -U postgres --owner=musicbrainz_user
- --encoding=UNICODE importtest
CREATE DATABASE
mbrainz$

Create the empty tables:
mbrainz$ psql -U musicbrainz_user importtest
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

importtest=> \i admin/sql/CreateTables.sql
BEGIN
psql:admin/sql/CreateTables.sql:31: NOTICE:  CREATE TABLE will create
implicit sequence "album_id_seq" for "serial" column "album.id"
CREATE TABLE
...
COMMIT
importtest=> \q
mbrainz$

Decompress the mbdump*.tar.bz2 files:
mbrainz$ tar jxf /path/to/mbdump.tar.bz2
(repeat for the other files too)

Import each file into the database:
mbrainz$ cd mbdump/
mbrainz$ mkdir ../done
mbrainz$ for t in * ; do echo `date` $t ; echo "\\copy $t from ./$t" |
psql -U musicbrainz_user importtest && mv $t ../done/ ; done ; echo
`date` Done
Sun Jun 5 19:46:22 BST 2005 album
...
Sun Jun 5 19:51:06 BST 2005 url
Sun Jun 5 19:51:09 BST 2005 Done
mbrainz$ cd ..

Add primary keys and indexes, and optimise the database:
mbrainz$ psql -U musicbrainz_user importtest
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

importtest=> \i admin/sql/CreatePrimaryKeys.sql
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"album_pkey" for table "album"
ALTER TABLE
...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"wordlist_pkey" for table "wordlist"
ALTER TABLE
importtest=> \i admin/sql/CreateIndexes.sql
CREATE INDEX
CREATE INDEX
...
CREATE INDEX
CREATE INDEX
importtest=> VACUUM ANALYZE;
importtest=> \q
mbrainz$

That's it!  If you want to, you could also choose to add the views
(CreateViews.sql).  In fact since we're not running
CreateFKConstraints.sql, the import is fairly quick too.  If you wanted
it even quicker, you might want to do without some of the indexes, and
just add the ones you think you need.

If you're planning on writing to the database (instead of just querying
from it), you might also want to set the correct sequence values (left
as an exercise for the reader) and run CreateFKConstraints.sql,
CreateFunctions.sql, and CreateTriggers.sql.

That probably belongs on the wiki too :-)

- --
Dave

PGP key: http://rudolf.org.uk/pgpkey
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCo1kunYOJTU6nkkkRAnFvAJ0SaIHUH6tOYtHWh5G1i8bFXgCV2wCeKxcZ
N816VI4MuLMgvDGyPS9PzkE=
=qlwE
-----END PGP SIGNATURE-----