History:Database Installation: Difference between revisions
PavanChander (talk | contribs) m (FIxed <small> formatting.) |
PavanChander (talk | contribs) (Better formatting, but broken list numbering.) |
||
Line 11: | Line 11: | ||
Install the base system. Install [[PostgreSQL]] (you need both the server and the client, if your system provides them separately). Install bzip2 and Subversion. Checkout the [[Server Download|MusicBrainz Server]] from [[Subversion]], and download the [[Database Download|MusicBrainz Database]] dumps, please make sure their revision matches. Configure postgresql the way you want it. |
Install the base system. Install [[PostgreSQL]] (you need both the server and the client, if your system provides them separately). Install bzip2 and Subversion. Checkout the [[Server Download|MusicBrainz Server]] from [[Subversion]], and download the [[Database Download|MusicBrainz Database]] dumps, please make sure their revision matches. Configure postgresql the way you want it. |
||
: ''(Here mbrainz is the UNIX user I'm running as; <code><nowiki>musicbrainz_user</nowiki></code> is the PostgreSQL user I connect as; <code><nowiki>importtest</nowiki></code> is the database I'm setting up).'' |
|||
</ul> |
|||
# '''Create the empty database:''' |
# '''Create the empty database:''' |
||
<pre>mbrainz$ createdb -U postgres --owner=musicbrainz_user |
|||
# '''Create the empty tables:''' <pre>mbrainz$ psql -U musicbrainz_user importtestWelcome 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 quitimporttest=> \i admin/sql/CreateTables.sqlBEGINpsql:admin/sql/CreateTables.sql:31: NOTICE: CREATE TABLE will createimplicit sequence "album_id_seq" for "serial" column "album.id"CREATE TABLE...COMMITimporttest=> \qmbrainz$</pre> |
|||
--encoding=UNICODE importtest |
|||
⚫ | |||
CREATE DATABASE |
|||
⚫ | |||
</ |
mbrainz$</pre> |
||
# '''Create the empty tables:''' |
|||
# '''Import each file into the database:''' <pre>mbrainz$ cd mbdump/mbrainz$ mkdir ../donembrainz$ for t in * ; do echo `date` $t ; echo "\\copy $t from ./$t" |psql -U musicbrainz_user importtest && mv $t ../done/ ; done ; echo `date` DoneSun Jun 5 19:46:22 BST 2005 album....Sun Jun 5 19:51:06 BST 2005 urlSun Jun 5 19:51:09 BST 2005 Donembrainz$ cd ..</pre> |
|||
<pre>mbrainz$ psql -U musicbrainz_user importtest |
|||
# '''Add primary keys and indexes, and optimise the database:''' <pre>mbrainz$ psql -U musicbrainz_user importtestWelcome 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 quitimporttest=> \i admin/sql/CreatePrimaryKeys.sqlNOTICE: 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 TABLEimporttest=> \i admin/sql/CreateIndexes.sqlCREATE INDEXCREATE INDEX...CREATE INDEXCREATE INDEXimporttest=> VACUUM ANALYZE;importtest=> \qmbrainz$</pre> |
|||
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$</pre> |
|||
# '''Decompress the mbdump*.tar.bz2 files:''' |
|||
⚫ | |||
⚫ | |||
# '''Import each file into the database:''' |
|||
<pre>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 ..</pre> |
|||
# '''Add primary keys and indexes, and optimise the database:''' |
|||
<pre>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$</pre> |
|||
That's it! If you want to, you could also choose to add the views ([http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateViews.sql CreateViews.sql]). In fact since we're not running [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateFKConstraints.sql 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. |
That's it! If you want to, you could also choose to add the views ([http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateViews.sql CreateViews.sql]). In fact since we're not running [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateFKConstraints.sql 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. |
||
Line 25: | Line 76: | ||
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 [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateFKConstraints.sql CreateFKConstraints.sql], [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateFunctions.sql CreateFunctions.sql], and [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateTriggers.sql CreateTriggers.sql]. |
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 [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateFKConstraints.sql CreateFKConstraints.sql], [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateFunctions.sql CreateFunctions.sql], and [http://bugs.musicbrainz.org/browser/mb_server/trunk/admin/sql/CreateTriggers.sql CreateTriggers.sql]. |
||
~- This information on installing the [[Database|MusicBrainz Database]] was by [[User:DaveEvans|DaveEvans]] in response to [[Debian VMWare Database|DebianVMWareDatabase]] (see [http://lists.musicbrainz.org/pipermail/musicbrainz-devel/2005-June/001140.html original post]). |
~- This information on installing the [[Database|MusicBrainz Database]] was by [[User:DaveEvans|DaveEvans]] in response to [[Debian VMWare Database|DebianVMWareDatabase]] (see [http://lists.musicbrainz.org/pipermail/musicbrainz-devel/2005-June/001140.html original post]). |
||
This method has been [http://lists.musicbrainz.org/pipermail/musicbrainz-devel/2005-June/001149.html reported] to work on a Macintosh. |
|||
[[Category:To Be Reviewed]] [[Category:Development]] [[Category:Database]] |
[[Category:To Be Reviewed]] [[Category:Development]] [[Category:Database]] |
Revision as of 10:38, 20 April 2009
Products > Database > Database setup > Database setup on Linux
How to Import the MusicBrainz Database
If you just want the data in a database, and you don't want Apache etc., here's another method.
- It is more flexible (you can choose not to bother creating the foreign key constraints, for example)
- It has fewer prerequisites (you do not 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 Subversion. Checkout the MusicBrainz Server from Subversion, and download the MusicBrainz Database dumps, please make sure their revision matches. 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.
~- This information on installing the MusicBrainz Database was by DaveEvans in response to DebianVMWareDatabase (see original post).
This method has been reported to work on a Macintosh.