Diff for "DatabaseSetup"

Not logged in - Log In / Register
Differences between revisions 1 and 9 (spanning 8 versions)
⇤ ← Revision 1 as of 2009-07-21 17:04:01 →
Size: 4756
Editor: mars
Comment:
← Revision 9 as of 2020-07-16 16:47:53 → ⇥
Size: 4281
Editor: doismellburning
Comment: Update script reference to drop Rocketfuel link as the page has gone, and the script is present regardless
Deletions are marked like this. Additions are marked like this.
Line 7: Line 7:
To set up postgres for running Launchpad, try using the '''`launchpad-database-setup`''' script from the Launchpad source tree.  If you have one (see RocketFuelSetup), you should find it in `utilities/`. To set up postgres for running Launchpad, try using '''`utilities/launchpad-database-setup`''' from the Launchpad source tree.
Line 9: Line 9:
The script expects a freshly installed postgresql database, so '''do not use this script if you are using postgres for anything besides Launchpad.''' It will __destroy__ any postgres databases you already have on your system. If that is a problem for you, use the manual procedure instead and adapt to your needs. '''WARNING: Do not use this script if you are using postgres for anything besides Launchpad.''' The script expects a freshly installed postgresql database, so it will __destroy__ any postgres databases you already have on your system. If that is a problem for you, use the manual procedure instead and adapt to your needs.
Line 13: Line 13:
These installation instructions apply to the new PostgreSQL database infrastructure as found in Ubuntu Breezy and later, and Debian Etch and later.
Line 17: Line 16:
 1. Shutdown any existing PostgreSQL instances you may have installed
    {{{
sudo -u postgres pg_ctlcluster 7.4 main stop
sudo -u postgres pg_ctlcluster 8.0 main stop
sudo -u postgres pg_ctlcluster 8.1 main stop
sudo -u postgres pg_ctlcluster 8.2 main stop
}}}		  1. '''If you have any versions of PostgreSQL other than 10 on your system:''' Launchpad requires PostgreSQL 10 on port 5432. Ensure that any existing PostgreSQL clusters are not running on port 5432.
Line 25: Line 18:
 1. '''If you have any other postgres databasese on your system:''' To avoid conflicting usage of ports by PostgreSQL 8.2 and any earlier versions, ensure that any existing PostgreSQL clusters are not running on port 5432.   Edit `/etc/postgresql/$OTHER_VERSION/*/postgresql.conf` and change the `port=` settings to 5433, 5434, 5435 etc.
Line 27: Line 20:
  Edit `/etc/postgresql/{7.4,8.0,8.1,8.2}/*/postgresql.conf` and change the `port=` settings to 5433, 5434, 5435 etc.

 For example, if you have postgresql 8.2 installed and nothing fancy (only one cluster) edit /etc/postgresql/8.2/main/postgresql.conf:		   For example, if you have postgresql 9.1 installed and nothing fancy (only one cluster) edit /etc/postgresql/9.1/main/postgresql.conf:
Line 38: Line 29:
  You may also want to edit `/etc/postgresql/{7.4,8.0,8.1,8.2}/*/start.conf to select which instances are run on startup, or even uninstall the earlier PostgreSQL versions.   You may also want to edit `/etc/postgresql/$OTHER_VERSION/*/start.conf to select which instances are run on startup, or even uninstall the earlier PostgreSQL versions.
Line 42: Line 33:
sudo aptitude install launchpad-database-dependencies sudo apt-get install launchpad-database-dependencies
Line 46: Line 37:
sudo aptitude install postgresql-8.3 postgresql-client-8.3 postgresql-contrib-8.3 postgresql-plpython-8.3 python-psycopg2 sudo aptitude install postgresql-10 postgresql-plpython-10 postgresql-client-10 postgresql-10-slony1-2 postgresql-10-debversion
Line 52: Line 43:
$ sudo pg_dropcluster 8.3 main --stop-server $ sudo pg_dropcluster 10 main --stop-server
Line 54: Line 45:
$ LC_ALL=C sudo pg_createcluster 8.3 main --start --encoding UNICODE $ LC_ALL=C sudo pg_createcluster 10 main --start --encoding UNICODE
Line 57: Line 48:
 1. add the following lines '''at the top''' of the file /etc/postgresql/8.3/main/pg_hba.conf:  1. Add the following lines '''at the top''' of the file /etc/postgresql/10/main/pg_hba.conf:
Line 63: Line 54:
host all all ::1/128 trust
Line 67: Line 59:
 1. Add the following options to /etc/postgresql/8.3/main/postgresql.conf:  1. Add the following options to /etc/postgresql/10/main/postgresql.conf:
Line 70: Line 62:
# Enable launchpad full text searching in database
search_path='"$user",public,ts2'
add_missing_from=false		 # Per Bug #90809, standard_conforming_strings should be 'on'
standard_conforming_strings=off
escape_string_warning=off
Line 74: Line 67:
log_statement='all' log_statement='none'
Line 77: Line 70:
max_fsm_relations = 2000
Line 80: Line 73:
    The first two are required. `enable_seqscan` helps tune queries as it forces PostgreSQL to use indexes if they are available (PostgreSQL often won't use them with our sample data because it is more efficient to simply scan the entire table), but will cause things to run a bit slower. The `log_*` statements output all queries sent to the server to `/var/log/postgresql/postgresql-8.3-main.log`, which helps you debug and understand what your code (and in particular SQLObject) is doing. The `fsync = off` line improves performance at the slightly increased risk of database integrity loss, by disabling fsyncs from postgresql. This change makes certain development tasks more efficient, for example, the amount of time required to create a new database can be dramatically reduced. In exchange, if your computer crashes, you would need to recreate the whole postgresql cluster.     The first two are required. `enable_seqscan` helps tune queries as it forces PostgreSQL to use indexes if they are available (PostgreSQL often won't use them with our sample data because it is more efficient to simply scan the entire table), but will cause things to run a bit slower. The `log_*` statements output all queries sent to the server to `/var/log/postgresql/postgresql-10-main.log`, which helps you debug and understand what your code (and in particular Storm) is doing. The `fsync = off` line improves performance at the slightly increased risk of database integrity loss, by disabling fsyncs from PostgreSQL. This change makes certain development tasks more efficient, for example, the amount of time required to create a new database can be dramatically reduced. In exchange, if your computer crashes, you would need to recreate the whole PostgreSQL cluster.
Line 82: Line 75:
 1. Restart the postgres server:  1. Restart the PostgreSQL server:
Line 85: Line 78:
sudo invoke-rc.d postgresql-8.3 restart sudo service postgresql restart
Line 88: Line 81:
 1. make sure your default PostgreSQL user is a superuser. Your default PostgreSQL user has the same name as your login account (if it existed already, and it was not a superuser, drop it first):  1. Make sure your default PostgreSQL user is a superuser. Your default PostgreSQL user has the same name as your login account (if it existed already, and it was not a superuser, drop it first):
Line 95: Line 88:
 1. create the databases, users, permissions and populate it with sampledata:  1. Create the databases, users, permissions and populate it with sampledata:

Launchpad Database Setup

There are two ways to set up your launchpad database.

Automated Launchpad Database Setup

To set up postgres for running Launchpad, try using utilities/launchpad-database-setup from the Launchpad source tree.

WARNING: Do not use this script if you are using postgres for anything besides Launchpad. The script expects a freshly installed postgresql database, so it will destroy any postgres databases you already have on your system. If that is a problem for you, use the manual procedure instead and adapt to your needs.

Manual Launchpad Database Setup

The initial Launchpad database should be done on your system with the following sequence of commands:

  1. If you have any versions of PostgreSQL other than 10 on your system: Launchpad requires PostgreSQL 10 on port 5432. Ensure that any existing PostgreSQL clusters are not running on port 5432.

    • Edit /etc/postgresql/$OTHER_VERSION/*/postgresql.conf and change the port= settings to 5433, 5434, 5435 etc. For example, if you have postgresql 9.1 installed and nothing fancy (only one cluster) edit /etc/postgresql/9.1/main/postgresql.conf: 

      port = 5432
      and replace that by 
      port = 5433
      You may also want to edit `/etc/postgresql/$OTHER_VERSION/*/start.conf to select which instances are run on startup, or even uninstall the earlier PostgreSQL versions.
  2. Install packages (Done by rocketfuel-setup, if you used that.): 
    • sudo apt-get install launchpad-database-dependencies
      which is equivalent to: 
      sudo aptitude install postgresql-10 postgresql-plpython-10 postgresql-client-10 postgresql-10-slony1-2 postgresql-10-debversion
  3. Nuke the default database and recreate with the current required locale: 
    • $ sudo pg_dropcluster 10 main --stop-server

$ LC_ALL=C sudo pg_createcluster 10 main --start --encoding UNICODE

  4. Add the following lines at the top of the file /etc/postgresql/10/main/pg_hba.conf: 

    • # allow unauthenticated connections to localhost
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

      Note that this gives all accounts on your local box full access to PostgreSQL - if this is a problem talk to StuartBishop for more detailed instructions (this requires occasional maintenance).

  5. Add the following options to /etc/postgresql/10/main/postgresql.conf: 
    • # Per Bug #90809, standard_conforming_strings should be 'on'
standard_conforming_strings=off
escape_string_warning=off

#enable_seqscan=false
log_statement='none'
log_line_prefix='[%t] %q%u@%d '
fsync = off

      The first two are required. enable_seqscan helps tune queries as it forces PostgreSQL to use indexes if they are available (PostgreSQL often won't use them with our sample data because it is more efficient to simply scan the entire table), but will cause things to run a bit slower. The log_* statements output all queries sent to the server to /var/log/postgresql/postgresql-10-main.log, which helps you debug and understand what your code (and in particular Storm) is doing. The fsync = off line improves performance at the slightly increased risk of database integrity loss, by disabling fsyncs from PostgreSQL. This change makes certain development tasks more efficient, for example, the amount of time required to create a new database can be dramatically reduced. In exchange, if your computer crashes, you would need to recreate the whole PostgreSQL cluster.

  6. Restart the PostgreSQL server: 
    • sudo service postgresql restart
  7. Make sure your default PostgreSQL user is a superuser. Your default PostgreSQL user has the same name as your login account (if it existed already, and it was not a superuser, drop it first): 
    • sudo -u postgres dropuser $(id -un)
sudo -u postgres createuser -s -d $(id -un)
  8. Create the databases, users, permissions and populate it with sampledata: 
    • cd $your_launchpad_checkout; make schema
  9. Profit! Enjoy your shiny new launchpad database.

DatabaseSetup (last edited 2020-07-16 16:47:53 by doismellburning)