Differences between revisions 1 and 23 (spanning 22 versions)
Revision 1 as of 2009-05-26 15:09:04
Size: 1961
Editor: SandroTosi
Comment:
Revision 23 as of 2015-03-18 10:57:21
Size: 6684
Comment:
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
= Create a Local Replica of UDD = ## page was renamed from UltimateDebianDatabase/CreateLocalReplica
#language en
= Getting the code =

Browse on [[http://anonscm.debian.org/gitweb/?p=collab-qa/udd.git|gitweb]]

{{{
# clone over git://
git clone git://git.debian.org/collab-qa/udd.git
}}}

{{{
# or over ssh:
git clone ssh://git.debian.org/git/collab-qa/udd.git
}}}

= Getting access to the DB =

There are two ways to get access to a UDD DB:
 * access it remotely from alioth.debian.org or ullmann.debian.org
  * [++] very easy to setup
  * [+] data is from the live instance
  * [--] queries are slow: loading dmd.cgi takes ~ 30s
 * setup postgresql locally and import a dump
  * [--] harder to setup
  * [++] queries are much faster
  * [-] needs manual updates to keep data up-to-date
  * [+] You can work offline

== Access it remotely ==

{{{
ssh -NL 5452:udd.debian.org:5452 aliothlogin@alioth.debian.org
}}}
Or directly on ullmann:
{{{
ssh -NL 5452:localhost:5452 debianlogin@ullmann.debian.org
}}}


== Create a Local Replica of UDD ==
Line 7: Line 47:
== Install Needed Packages ==

UDD runs on PostgreSql, so we need to install it (if it's not already available):

{{{
sudo apt-get install postgresql
}}}

that installs always the current supported version (at the time of writing it's 8.3).

Then we need also to install:

{{{
$ apt-cache search plperl
postgresql-plperl-8.3 - PL/Perl procedural language for PostgreSQL 8.3
$ sudo apt-get install postgresql-plperl-8.3
}}}

and

{{{
$ apt-cache search debversion
postgresql-8.3-debversion - Debian version number type for PostgreSQL
$ sudo apt-get install postgresql-8.3-debversion
}}}

needed for packages version comparison.

== Setup PostgreSql Server ==

Now we got an "empty" PostreSql db server, let's set up it a bit.

First we create some users/roles
=== Install Needed Packages ===

UDD runs on PostgreSQL, so we need to install it plus the necessary extensions for UDD:

{{{
sudo apt-get install postgresql postgresql-plperl-9.4 postgresql-9.4-debversion
}}}

In case the latter two packages don’t work, you might need to upgrade the version number.

=== Setup PostgreSQL Server ===

Modify /etc/postgresql/9.4/main/pg_hba.conf to trust local connections:
{{{
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
}}}

Modify /etc/postgresql/9.4/main/postgresql.conf to listen on port 5452 (same as the real UDD instance on ullmann.debian.org):
{{{
port = 5452
}}}

Restart postgresql with this new configuration:
{{{
service postgresql restart
}}}

Now we got an "empty" PostgreSQL db server, let's import some bits.

First we create some users/roles by switching to the {{{postgres}}} user which is used for database administration, then running createuser:
Line 43: Line 85:
$ createuser morph
Shall the new role be a superuser? (y/n) y
}}}

This is my user, with superuser powers to easy management, create your own.

{{{
Line 56: Line 91:

Line 58: Line 95:
We need to do these configurations with the user <code>postgres</code>, the owner of the db instance.

{{{
$ createdb -E SQL_ASCII udd
}}}

We create the database, named 'udd', forcing the encoding to SQL_ASCII, since that's teh format of the export.

== Import UDD Dump ==
{{{
$ createdb -T template0 -E SQL_ASCII udd
$ echo 'CREATE EXTENSION debversion' | psql udd
}}}

We create the database, named 'udd', forcing the encoding to SQL_ASCII, since that's the format of the export.
We base it off 'template0' because 'template1' (the default) might be set to UTF8 which prevents creation of new SQL_ASCII databases.

UDD is actually using a "guest" user:
{{{
createuser -lDRS guest
psql udd -c 'GRANT usage ON schema public TO PUBLIC;'
psql udd -c 'GRANT select ON all tables in schema public TO PUBLIC;'
}}}

=== Import UDD Dump ===
Line 71: Line 115:
zcat /path/to/udd.sql.gz | psql udd
}}}

that will import the dump into udd db.
$ zcat /path/to/udd.sql.gz | sudo -u postgres psql udd 2>&1 | tee udd-import.log
}}}

that will import the dump into udd db redirecting messages to udd-import.log.

In case of error messages, you may identify the problematic query by issueing again the psql command with the "-e" option :
{{{
$ zcat /path/to/udd.sql.gz | sudo -u postgres psql -e udd 2>&1 | tee udd-import.log
}}}

=== Reimport UDD ===

If we already have a UDD replicated on our local machine and want to reimport the UDD dump, we need to:

{{{
$ sudo su postgres
$ dropdb udd
$ createdb -T template0 -E SQL_ASCII udd
$ echo 'CREATE EXTENSION debversion' | psql udd
$ zcat /path/to/udd.sql.gz | psql udd
}}}

so simply drop the db, recreate it and import the dump again.

= Setup a local web server =

Install some Ruby packages, needed for dmd.cgi
{{{
sudo apt-get install ruby-dbi ruby-dbd-pg ruby-debian ruby-oj vpim
}}}

== Apache2 ==
Here is guidelines to provide UDD over localhost using '''apache2'''

{{{
sudo apt-get install apache2-mpm-worker
}}}

Minimal configuration that works, edit /etc/apache2/sites-enabled/000-default in the <!VirtualHost> declaration, add

{{{
Alias /udd /path/to/udd/web
 <Location /udd>
  AddHandler cgi-script .cgi
  Options +ExecCGI +Indexes
                # For wheezy / Apache 2.2:
  Order allow,deny
  Allow from all
                # For jessie / Apache 2.4:
                Require all granted
 </Location>
}}}

Restart apache with this new configuration
{{{
sudo service apache2 restart
}}}

 * Try: http://localhost/udd/dmd.cgi


== NGinx ==
If you want to use Nginx, setup is a bit more difficult because NGinx don't support CGI out-of-the-box.

{{{
sudo apt-get install nginx-full fcgiwrap
}}}

Minimal configuration that works, edit /etc/nginx/sites-available/default and replace with :

{{{
server {
        listen 80 default_server;

        root /path/to/udd/web;
        index index.html index.htm;
        server_name localhost;

        location / {
                # First attempt to serve request as file, then
                # as directory, then fall back to displaying a 404.
                try_files $uri $uri/ /index.html;
        }

        location ~ \.cgi$ {
          gzip off;
          fastcgi_pass unix:/var/run/fcgiwrap.socket;
          include /etc/nginx/fastcgi_params;
          # fastcgi_param SCRIPT_FILENAME /usr/lib$fastcgi_script_name;
        }
}
}}}

Restart nginx with this new configuration
{{{
sudo service nginx restart
}}}

 * Try: http://localhost/dmd.cgi

== Common problems ==
  * check file-system permissions (must be world-readable, or at least readable by www-data)
  * ensure you are running PostgreSQL on 5452 port
  * check that there is a "guest" account on PostgreSQL

= Hack, hack, hack =

Please submit patches to [[mailto:debian-qa@lists.debian.org|debian-qa]] mailing list.

Bugs can be filed against the [[DebianBug:qa.debian.org]] pseudo-package, with user '''qa.debian.org@packages.debian.org''' and usertag '''udd'''. See [[DebianBug:692943|this bug]] for an example.


(see [[DebianBug:637524|this bug]] for the correct pseudo-headers).

Getting the code

Browse on gitweb

# clone over git://
git clone git://git.debian.org/collab-qa/udd.git

# or over ssh:
git clone ssh://git.debian.org/git/collab-qa/udd.git

Getting access to the DB

There are two ways to get access to a UDD DB:

  • access it remotely from alioth.debian.org or ullmann.debian.org
    • [++] very easy to setup
    • [+] data is from the live instance
    • [--] queries are slow: loading dmd.cgi takes ~ 30s
  • setup postgresql locally and import a dump
    • [--] harder to setup
    • [++] queries are much faster
    • [-] needs manual updates to keep data up-to-date
    • [+] You can work offline

Access it remotely

ssh -NL 5452:udd.debian.org:5452 aliothlogin@alioth.debian.org

Or directly on ullmann:

ssh -NL 5452:localhost:5452 debianlogin@ullmann.debian.org

Create a Local Replica of UDD

As we can see from UDD main page, there is a dump available.

So if we want to do some test, maybe a local replica of UDD can be handy, and that's what this page will describe.

Install Needed Packages

UDD runs on PostgreSQL, so we need to install it plus the necessary extensions for UDD:

sudo apt-get install postgresql postgresql-plperl-9.4 postgresql-9.4-debversion

In case the latter two packages don’t work, you might need to upgrade the version number.

Setup PostgreSQL Server

Modify /etc/postgresql/9.4/main/pg_hba.conf to trust local connections:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Modify /etc/postgresql/9.4/main/postgresql.conf to listen on port 5452 (same as the real UDD instance on ullmann.debian.org):

port = 5452

Restart postgresql with this new configuration:

service postgresql restart

Now we got an "empty" PostgreSQL db server, let's import some bits.

First we create some users/roles by switching to the postgres user which is used for database administration, then running createuser:

$ sudo su postgres
$ createuser udd
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

This is the UDD user on the database, where the UDD objects will live.

$ createdb -T template0 -E SQL_ASCII udd
$ echo 'CREATE EXTENSION debversion' | psql udd

We create the database, named 'udd', forcing the encoding to SQL_ASCII, since that's the format of the export. We base it off 'template0' because 'template1' (the default) might be set to UTF8 which prevents creation of new SQL_ASCII databases.

UDD is actually using a "guest" user:

createuser -lDRS guest
psql udd -c 'GRANT usage ON schema public TO PUBLIC;'
psql udd -c 'GRANT select ON all tables in schema public TO PUBLIC;'

Import UDD Dump

Now we only need to download the dump and import it, with:

$ zcat /path/to/udd.sql.gz | sudo -u postgres psql udd 2>&1 | tee udd-import.log

that will import the dump into udd db redirecting messages to udd-import.log.

In case of error messages, you may identify the problematic query by issueing again the psql command with the "-e" option :

$ zcat /path/to/udd.sql.gz | sudo -u postgres psql -e udd 2>&1 | tee udd-import.log

Reimport UDD

If we already have a UDD replicated on our local machine and want to reimport the UDD dump, we need to:

$ sudo su postgres
$ dropdb udd
$ createdb -T template0 -E SQL_ASCII udd
$ echo 'CREATE EXTENSION debversion' | psql udd
$ zcat /path/to/udd.sql.gz | psql udd

so simply drop the db, recreate it and import the dump again.

Setup a local web server

Install some Ruby packages, needed for dmd.cgi

sudo apt-get install ruby-dbi ruby-dbd-pg ruby-debian ruby-oj vpim

Apache2

Here is guidelines to provide UDD over localhost using apache2

sudo apt-get install apache2-mpm-worker

Minimal configuration that works, edit /etc/apache2/sites-enabled/000-default in the <VirtualHost> declaration, add

Alias /udd /path/to/udd/web
        <Location /udd>
                AddHandler cgi-script .cgi
                Options +ExecCGI +Indexes
                # For wheezy / Apache 2.2:
                Order allow,deny
                Allow from all
                # For jessie / Apache 2.4:
                Require all granted
        </Location>

Restart apache with this new configuration

sudo service apache2 restart

NGinx

If you want to use Nginx, setup is a bit more difficult because NGinx don't support CGI out-of-the-box.

sudo apt-get install nginx-full fcgiwrap

Minimal configuration that works, edit /etc/nginx/sites-available/default and replace with :

server {
        listen 80 default_server;

        root /path/to/udd/web;
        index index.html index.htm;
        server_name localhost;

        location / {
                # First attempt to serve request as file, then
                # as directory, then fall back to displaying a 404.
                try_files $uri $uri/ /index.html;
        }

        location ~ \.cgi$ {
          gzip off;
          fastcgi_pass  unix:/var/run/fcgiwrap.socket;
          include /etc/nginx/fastcgi_params;
          # fastcgi_param SCRIPT_FILENAME  /usr/lib$fastcgi_script_name;
        }
}

Restart nginx with this new configuration

sudo service nginx restart

Common problems

  • check file-system permissions (must be world-readable, or at least readable by www-data)
  • ensure you are running PostgreSQL on 5452 port
  • check that there is a "guest" account on PostgreSQL

Hack, hack, hack

Please submit patches to debian-qa mailing list.

Bugs can be filed against the qa.debian.org pseudo-package, with user qa.debian.org@packages.debian.org and usertag udd. See this bug for an example.

(see this bug for the correct pseudo-headers).