Notes about PostgreSQL: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
mNo edit summary
Line 7: Line 7:


* don't install as a service
* don't install as a service
initdb
"postgres" -D "petra"
psql
  CREATE USER tom WITH PASSWORD 'myPassword';


  You might want to set the environment variables before creating the database:
  You might want to set the environment variables before creating the database:
  set PGUSER=postgres
  set PGUSER=postgres
  set PGPASSWORD=myPassword
  set PGPASSWORD=myPassword
From the Postgres program folder (e.g. C:\Program Files\PostgreSQL\9.0\bin\) run the following in a command window:
initdb -D "petra"
postgres -D "petra"
psql -c "CREATE USER tom WITH PASSWORD 'myPassword';"


Or alternatively, write the file pgpass.conf; see http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html
Or alternatively, write the file pgpass.conf; see http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html

Revision as of 09:55, 21 July 2011

installation

Windows

  • don't install as a service
You might want to set the environment variables before creating the database:
set PGUSER=postgres
set PGPASSWORD=myPassword
From the Postgres program folder (e.g. C:\Program Files\PostgreSQL\9.0\bin\) run the following in a command window:
initdb -D "petra"
postgres -D "petra"
psql -c "CREATE USER tom WITH PASSWORD 'myPassword';"

Or alternatively, write the file pgpass.conf; see http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html

nant recreateDatabase
nant resetDatabase

A third option is using the program pgAdmin. The new user needs to have privilege to create a new database.

In case you forgot your postgres-Password do fe. the following:

- Uninstall PostgreSQL
- execute the following command on the windows command line: NET USER postgres /DELETE
- Reinstall PostgreSQL

Linux

  • if you get this error message when starting the OpenPetra server:
    • no pg_hba.conf entry for host "xx.yyy.zz.xx", user "petraserver", database "openpetra", SSL off
    • solution: add to file /etc/postgresql/8.3/main/pg_hba.conf:
host   openpetra  petraserver   xx.yyy.zz.xx/0   md5
    • and restart postgresql

Some notes

  • drop database but tables are still there when creating the database again: [1]; reason: tables are in template1 db, and that is used as a template for creating the new database
  • drop all tables in db: DROP SCHEMA public CASCADE; [2]
    • you should create a new schema public again (especially if this is the template1 db): CREATE SCHEMA public;


  • Hide notice during nant recreateDatabase: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "..." for table "..."
  • If you get problems logging in to the database via nant recreatDatabase then please look here