This often comes when a postgres user was already created and not removed.
The best way I found, is to uninstall Postgresql DB, remove the postgres user, and reinstall the new version.
sudo dscl . delete /users/postgres
Note, when installing Postgresql in the Advanced Option, nice to have everybody in the team choose the same local, e.g. “en_US.UTF-8″
# create the user CREATE USER some_user WITH password 'pwd' # create the new db with the new user as owner CREATE DATABASE some_db WITH OWNER some_user; # connect to db \connect some_db # create schema CREATE SCHEMA some_schema AUTHORIZATION some_user;
1. Change the password in single user mode 2. Edit pg_hba.conf and set it to trust mode, change your password, change back to md5 3. Re-initdb your cluster. For 1: su - postgres (from root if you have to) pg_ctl stop postgres template1 (or some other database you know exists) alter user postgres password 'newpassword'; For 2: su - postgres cd $PGDATA vi pg_hba.conf // change entries at bottom to trust pg_ctl reload psql template1 alter user postgres password 'newpassword'; For 3: su - postgres pg_ctl stop echo $PGDATA // make sure this is set to something like /var/lib/pgsql rm -rf $PGDATA/* initdb // with whatever options you need.
SET search_path TO myschema;
su - postgres psql #or psql mydb
su - postgres createdb DBTest
Backup: $ pg_dump {source_db} -f {dumpfilename.sql}
Restore: $ psql -d {desintation_db} -f {dumpfilename.sql}
Note: on Mac OS X, the command prompt need to be started from the application folder or from pgAdminIII, and then, type “\!” to switch to command shell, and then, go to /Library/PostgreSQL/8.4/bin to find the psql and pg_dump scripts
How to backup and restore postgresql
Somehow, my Postgresql 8.4.3 failed to start on windows. After some digging around, I found the following in the windows event log:
FATAL: bogus data in lock file "postmaster.pid": ""
Removing the file did the trick (it is located at the root folder of your database store)
@Table(name = "mySchema.user")
@javax.persistence.SequenceGenerator(
name="SEQ_STORE",
allocationSize=1,
sequenceName="mySchema.user_id_seq"
)
public class User {
....
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_STORE")
public Long getId() {
return id;
}
}
UPDATE for 9.x, Look this link: Installing Postgres 9.x with Yum
First check your latest version of the noarch.rpm at yum postgres repos
for CentOS 5, 64bit, Postgres 9.1
$ wget http://yum.pgrpms.org/9.1/redhat/rhel-5-x86_64/pgdg-centos91-9.1-4.noarch.rpm $ rpm -ivh pgdg-centos91-9.1-4.noarch.rpm
yum list | grep postgresql
If you see postgresql from other repositories besides pgdg84, then you need to exclude postgresql from coming from other repositories by following the below instructions excerpted from PostgreSQL How to Yum
Add to the bottom of the section:
exclude=postgresql*
yum install postgresql-server
service postgresql initdb # or service postgresql-9.1 initdb
chkconfig postgresql on
su - postgres createdb DBTest
su - postgres psql DBTest
Root directory: /var/lib/pgsql/
Config file: /var/lib/pgsql/data/postgresql.conf