Manual:PostgreSQL

From MediaWiki.org
Jump to: navigation, search
Translate this page; This page contains changes which are not marked for translation.

Other languages:
Deutsch • ‎English • ‎日本語 • ‎polski • ‎português • ‎português do Brasil • ‎русский • ‎中文

The purpose of this page is to collect information and experiences about running MediaWiki on PostgreSQL.

MediaWiki supports PostgreSQL since 1.7. See Phabricator for a list of issues. MediaWiki requires PostgreSQL 8.3 or later, and PG database support enabled in PHP.

Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.

Is PostgreSQL a good choice for MediaWiki?[edit]

Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.

MediaWiki database migration[edit]

From MySQL to PostgreSQL[edit]

The MWDumperManual:MWDumper tool might help.

There is also a mediawiki_mysql2postgres.pl perl script in maintenance/postgres, but there is uncertainty whether it works.

From PostgreSQL to MySQL[edit]

This is generally not recommended as converting from Postgres data types back to MySQL is not as simple an operation as the reverse.

There are however some scripts which may work.

Installation of MediaWiki with PostgreSQL and Nginx[edit]

The methods below briefly describe ways to install MediaWiki 1.25MediaWiki 1.25 with PostgreSQL 9.5.2 and Nginx 1.8 from their source on Ubuntu 14.04.

For cleaner and simpler maintenance, all the programs are installed in /home/s/src

cd /home/s
mkdir -p {src,bin,logs,daba,web}

##
## Install packages required by PostgreSQL 9.5.2
##
sudo apt-get install \
  zlib1g             \
  zlib1g-dev         \
  libreadline6-dev   \
  libssl-dev         \
  docbook            \
  docbook-dsssl      \
  docbook-xsl        \
  openjade           \
  xsltproc           \
  opensp             \
  libxml2-utils

##
## Install packages required by Nginx 1.8
##
sudo apt-get install \
  libpcre3           \
  libpcre3-dev       \
  zlib1g             \
  zlib1g-dev         \
  openssl            \
  libssl-dev         \

##
## Install packages required by PHP 5.6.13
##
sudo apt-get install \
  libxml2-dev        \
  icu-devtools       \
  libicu-dev         \
  libcurl4-gnutls-dev

##
## PHP could only use bison 2.7.1
##
wget http://launchpadlibrarian.net/140087283/libbison-dev_2.7.1.dfsg-1_amd64.deb
wget http://launchpadlibrarian.net/140087282/bison_2.7.1.dfsg-1_amd64.deb
sudo dpkg -i libbison-dev_2.7.1.dfsg-1_amd64.deb
sudo dpkg -i bison_2.7.1.dfsg-1_amd64.deb
rm -rf libbison-dev_2.7.1.dfsg-1_amd64.deb bison_2.7.1.dfsg-1_amd64.deb

# Prevent software update manager to overwrite these two packages
sudo apt-mark hold libbison-dev
sudo apt-mark hold bison

##
##  PostgreSQL 9.5.2
##
cd /home/s/src
wget https://ftp.postgresql.org/pub/source/v9.5.2/postgresql-9.5.2.tar.bz2
tar -jxf postgresql-9.5.2.tar.bz2

cd postgresql-9.5.2
mkdir Release && cd Release
../configure --prefix=/home/s --with-openssl 
make world
make install-world 

mkdir /home/s/daba

/home/s/bin/initdb -D /home/s/daba

# Modify /home/s/daba/[postgresql.conf, pg_hba.conf]

# Modify /home/s/src/postgresql-9.5.2/contrib/start-scripts/linux into
# /etc/init.d/postgresql for automatic startup

pg_ctl start

##
## XCache 3.2.0
##
cd /home/s/src
wget http://xcache.lighttpd.net/pub/Releases/3.2.0/xcache-3.2.0.tar.gz
tar -zxf xcache-3.2.0.tar.gz -C /home/s/src
cd /home/s/src/xcache-3.2.0
phpize
configure --prefix=/home/s --enable-xcache
make
make install
cat xcache.ini >> /home/s/lib/php.ini

# Modify /home/s/lib/php.ini

##
## PHP 5.6.13
##
cd /home/s/src
git clone https://github.com/php/php-src.git php
cd php
git checkout PHP-5.6.13
./buildconf --force
mkdir Release && cd Release
../configure                 \
--prefix=/home/s             \
--mandir=/home/s/share/man   \
--with-openssl               \
--with-pdo-pgsql=/home/s     \
--with-pgsql=/home/s         \
--enable-opcache             \
--enable-mbstring            \
--enable-intl                \
--enable-fpm                 \
--enable-sockets             \
--enable-pcntl               \
--with-curl                  \
--localstatedir=/home/s/logs \
--sbindir=/home/s/bin        \
--datadir=/home/s/logs       \
--sysconfdir=/home/s/conf    \
--with-fpm-user=www-data     \
--with-fpm-group=www-data

make all
make install

# Update /home/s/lib/php.ini

cd /home/s/bin
curl -sS https://getcomposer.org/installer | \
php -- --install-dir=/home/s/bin
ln -s /home/s/bin/composer.phar composer

# Modify /home/s/src/php/Release/sapi/fpm/init.d.php-fpm into
# /etc/init.d/php-fpm for automatic startup

# Modify /home/s/conf/php-fpm.conf.default into
# /home/s/conf/php-fpm.conf

##
## Nginx 1.8
##
cd /home/s/src
git clone https://github.com/nginx/nginx.git
cd nginx
git checkout branches/stable-1.8     # Checkout latest stable branch
mkdir Release
./auto/configure                                           \
--prefix=/home/s                                           \
--builddir=./Release                                       \
--sbin-path=/home/s/bin                                    \
--with-http_ssl_module                                     \
--with-pcre                                                \
--with-http_realip_module                                  \
--with-http_addition_module                                \
--with-http_mp4_module                                     \
--with-http_flv_module                                     \
--with-ipv6                                                \
--with-mail                                                \
--with-mail_ssl_module                                     \
--with-http_stub_status_module                             \
--http-client-body-temp-path=/home/s/tmp/client_body_temp/ \
--http-proxy-temp-path=/home/s/tmp/proxy_temp              \
--http-fastcgi-temp-path=/home/s/tmp/fastcgi_temp          \
--http-uwsgi-temp-path=/home/s/tmp/uwsgi_temp              \
--http-scgi-temp-path=/home/s/tmp/scgi_temp

make
make install
mv /home/s/html/* /home/s/web
rm -rf /home/s/html

# Choose and modify a suitable nginx.conf into /home/s/conf/

# Choose and modify a suitable fastcgi_params into /home/s/conf/

# Get a suitable script to start nginx automatically

##
## MediaWiki 1.25
##
cd /home/s/src
mkdir mediawiki && cd mediawiki
git clone https://gerrit.wikimedia.org/r/p/mediawiki/core.git
cd core
git checkout REL1_25
mkdir -p /home/s/web/w
cd /home/s/src/mediawiki/core
tar -cf - --exclude '.git*' . | (cd /home/s/web/w; tar -xpf -)
cd /home/s/web/w
composer update
cd /home/s/web/w/skins
git clone https://gerrit.wikimedia.org/r/p/mediawiki/skins/Vector.git

# Create role `wikiroot` and database `mywiki` in PostgreSQL
createuser -PE wikiroot
createdb -O wikiroot mywiki

# Connect to database mywiki with
#    psql -d mywiki
# Execute these
#    ALTER DATABASE mywiki   SET search_path TO mywiki, public;
#    ALTER ROLE     wikiroot SET search_path TO mywiki, public;

# Open http://localhost/w/index.php
# Follow the instruction

Installation of MediaWiki with PostgreSQL and Nginx on Ubuntu 16.04[edit]

This also works in Debian 9, if su is used instead of sudo.

  1. Install the required packages sudo apt install postgresql nginx php-fpm php-apcu php-gd php-intl php-mbstring php-pgsql php-xml git
  2. Edit /etc/nginx/sites-available/default and add index.php and enable PHP FPM
            index index.html index.htm index.nginx-debian.html index.php;
    
            # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
            #
            location ~ \.php$ {
                    include snippets/fastcgi-php.conf;
    
            #       # With php7.0-cgi alone:
            #       fastcgi_pass 127.0.0.1:9000;
                    # With php7.0-fpm:
                    fastcgi_pass unix:/run/php/php7.0-fpm.sock;
            }
    
  3. Restart nginx sudo systemctl restart nginx
  4. Create a database user sudo -u postgres createuser -PE wikiroot and database sudo -u postgres createdb -O wikiroot my_wiki
  5. Go to the Nginx root cd /var/www/html and clone MediaWiki sudo git clone --recurse-submodules -b REL1_29 https://gerrit.wikimedia.org/r/p/mediawiki/core.git mediawiki
  6. Change the owner of images to Nginx sudo chown -R www-data:www-data mediawiki/images/ to allow file uploads
  7. Open a web browser to http://localhost/mediawiki and start the MediaWiki configuration
DatabasesManual:Database layout Engines: MySQL/MariaDBManual:MySQLOracleManual:OraclePostgreSQLManual:PostgreSQLSQLiteManual:SQLiteSQL ServerManual:Microsoft SQL Server

Technical documentation: SchemaManual:Database layout (tablesCategory:MediaWiki database tables) – API property associationsAPI:Database field and API property associationsField prefixesDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsWikimedia extension tablesCategory:Wikimedia extension database tables
Configuration: SettingsManual:Configuration_settings#Database_settingsSharingManual:Shared database
Development: AccessManual:Database accessOptimizationDatabase optimizationPolicyDevelopment policy#Database_patchesUpdaterManual:DatabaseUpdater.phpExtension schema updatesManual:Hooks/LoadExtensionSchemaUpdatesPatch fileManual:SQL patch file

Core tables: archivebot_passwordscategorycategorylinkschange_tagcommentconfigexternallinksfilearchivehitcounterimageimagelinksimage_comment_tempinterwikiiwlinksipblocksip_changesjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionrevision_comment_tempsearchindexsitessite_identifiers - site_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist