From MediaWiki.org
Jump to: navigation, search
This page is a translated version of the page Manual:PostgreSQL and the translation is 32% complete.

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 obsługuje PostgreSQL od wersji 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.

Czy PostgreSQL to dobry wybór dla MediaWiki?

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.

Migracji bazy danych MediaWiki

Z MySQL do PostgreSQL

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.

Z PostgreSQL do MySQL

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

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             \

## 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         \

## PHP could only use bison 2.7.1
wget https://launchpadlibrarian.net/140087283/libbison-dev_2.7.1.dfsg-1_amd64.deb
wget https://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 https://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
configure --prefix=/home/s --enable-xcache
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     \

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              \

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 --no-dev
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

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
            location ~ \.php$ {
                    include snippets/fastcgi-php.conf;
            #       # With php7.0-cgi alone:
            #       fastcgi_pass;
                    # 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
Bazy danychManual:Database layout Silniki: MySQL/MariaDBManual:MySQLOracleManual:OraclePodręcznik:PostgreSQLManual:PostgreSQLPodręcznik:SQLiteManual:SQLiteSQL ServerManual:Microsoft SQL Server

Dokumentacja techniczna: SchematyManual:Database layout (tabeleCategory:MediaWiki database tables) – Przypisane właściwości w APIAPI:Database field and API property associationsPrefiksy pólDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsTabele rozszerzeń WikimediaCategory:Wikimedia extension database tables
Konfiguracja: UstawieniaManual:Configuration_settings#Database_settingsUdostępnianieManual:Shared database
Rozwój: DostępManual:Database accessOptymalizacjaDatabase optimizationZasadyDevelopment policy#Database_patchesAktualizatorManual:DatabaseUpdater.phpAktualizacje schematów rozszerzeńManual:Hooks/LoadExtensionSchemaUpdatesPlik poprawekManual:SQL patch file

Główne tabele: actorarchivebot_passwordscategorycategorylinkschange_tagcommentconfigexternallinksfilearchivehitcounterimageimagelinksimage_comment_tempinterwikiiwlinksipblocksip_changesjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionrevision_comment_tempsearchindexsitessite_identifierssite_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist