- [Show pagesource]
- [Old revisions]
- [[unknown link type]]
- []
Table of Contents
Quick start
\d pg_database select * from pg_database;
select * from pg_user
This PhpWiki pgsql install is perfect tutorial for PostgreSQL basics.
by Hans Schou 2003-04-16 A short description of how I got PhpWiki to work with PostgreSQL.
Related to PhpWiki version 1.3.4.
In the following some user defined names are used. Search and replace the names with what you want to use:
localhost Domain name for PhpWiki and PostgreSQL dschou_dk Name of PostgreSQL database uschou_dk Username of owner of database pschou_dk Password for database pwadmin PhpWiki administrator foobar PhpWiki administrator password rtGdfs8dp PhpWiki administrator password encrypted pw_ Table name prefix
Install packages
This is the package names used in Mandrake 9.0.
postgresql-server php-pgsql
Give user postgres a password
su - passwd postgres
Change pgsql connect method
We want all users listed in PostgreSQLs user list to be able to connect. Only users local on the same machine can connect - no TCP/IP connections. Login as user 'postgres'
su - su - postgres
In /var/lib/pgsql/data/pg_hba.conf change auth to:
local all password
Restart PostgreSQL
su - -c "/etc/init.d/postgresql restart"
Create a user in PostgreSQL
Login as a user which can create users and databases.
su - postgres createuser -A -D -P uschou_dk
(password is 'pschou_dk' in this example)
Create a database for user uschou_dk
You could use the database owned by the Apache user but I don't like it that way.
su - postgres createdb dschou_dk
Change owner of database 'dschou_dk'
(There must be an easier way to do it)
su - postgres psql -c "UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='uschou_dk') WHERE datname='dschou_dk'"
Check everything is correct:
psql -l | grep dschou_dk dschou_dk | uschou_dk | SQL_ASCII
Change user and prefix in schemas/psql.sql
Edit schemas/psql.sql
\set prefix 'pw_' \set httpd_user 'uschou_dk'
Create phpwiki tables
cd phpwiki psql -W -d dschou_dk -U uschou_dk < schemas/psql.sql
(I get some errors here, so run it twice)
Create an encrypted ADMIN password
Start your “any browser”:
lynx http://localhost/phpwiki/passencrypt.php
(enter foobar and get the encrypted text) Copy the encrypted password into index.php:
if (!defined('ADMIN_USER')) define('ADMIN_USER', "pwadmin"); if (!defined('ADMIN_PASSWD')) define('ADMIN_PASSWD', "rtGdfs8dp"); if (!defined('ENCRYPTED_PASSWD')) define('ENCRYPTED_PASSWD', true);
Change phpwiki index.php
cd phpwiki
Edit index.php
'dbtype' => 'SQL', 'dsn' => 'pgsql://uschou_dk:pschou_dk@socket()/dschou_dk', 'prefix' => 'pw_',
Hint, if you want to connect via TCP:
'dsn' => 'pgsql://uschou_dk:pschou_dk@localhost/dschou_dk',
or more specific with protocol and port number:
'dsn' => 'pgsql://uschou_dk:pschou_dk@tcp()localhost:5432/dschou_dk',
Fire up PhpWiki
First create some default pages:
lynx http://localhost/phpwiki/
Check it out:
lynx http://localhost/phpwiki/
Login as administrator:
Login: pwadmin Password: foobar
How much space does PhpWiki use?
DATID=$(psql -t -c "SELECT datid FROM pg_stat_database WHERE datname='dschou_dk'") du -h /var/lib/pgsql/data/base/$DATID
Migrating from one server to another
pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname
* http://stackoverflow.com/questions/1237725/how-to-copy-postgres-database-to-another-server
Verze pro scripty
pgdump -U user -h localhost dbname | bzip2 > dbname.sql.bz
Pokud se prikaz vola v cronu pod rootem, je nutne vnutit postgresu heslo na uzivatele, krery ma k dumpovane db pristup (byt by to byl postgres). To se nastavuje v souboru /root/.pgpass a to ve tvaru hostname:port:database:username:password, tzn. *:*:*:postgres:passwd . Pozor, sooubor musi mit prava 0600. jinak jej dump ignoruje!
Pokud narazite na chyby s LOCK relation, je treba zmenit uzivatele co dumpuje, nebo ptidat tomu soucasnemu spousta GRANT SELECT, nebo z nej udelat superusera.
Mazani foreign keys
Nekdy se trochu nestatne stane, ze potrebujete smazat neco, co ma foreign key na tabulku, ktera ho ma na to, co chcete smazat. Takze Vam jedno nedovoli smazat druhe kvlu zavislosti a stejne tak naopak. Na to existuje hezky tricek, kdy kontrolu zavislosti nechate az na konec, kdy je vse smazane a tudiz ok :)
BEGIN TRANSACTION DEFERRABLE; DELETE FROM a WHERE id='666'; DELETE FROM b WHERE di='999'; COMMIT;