Out client was using PostgreSQL for their database we often have a hard time moving our project repository from one server to another. The problem was that we need to manage username and passwords. When we are rushed to setup a working copy of the website, we run into trouble.
First, the rental server has no PostgreSQL pre-installed, so we have to install it.
I uploaded the installer to the server via ftp and extract it with this command.
tar -xvzf postgres-source.tar.gz cd postgres-source
And this is the short version of how to install PostgreSQL from source.
./configure gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test
Well, so you think we’ve done it right huh? The problem is that when I tried to login to psql, it prompts a password where it shouldn’t. The problem was, I’m not the one who did the installation. So I take over and restart the process.
Before I re-install everything, I need to cleanup everything the install process created. So I once again get my hands dirty on the keyboard.
gmake uninstall # to search all related files that are not removed by uninstall cd / find -name *psql* # delete everything found on the search find -name *pgsql* #delete everything found
Then I restart the install process. It works!
su su - postgres psql # it don't ask password anymore #exit
Now for user management:
# inside psql shell create role role_name; create role role_name LOGIN; # enables the role to login create role test_user LOGIN CREATEDB CREATEROLE PASSWORD 'thePass'; create role test_superuser SUPERUSER PASSWORD 'thePass';
Now, if for some reason we forget our postgres password, we will reset it by editing /var/lib/pgsql/data/pg_hba.conf. Restart postgres. Login and reset password. Restart again, change back to secured mode and restart.
su nano /var/lib/pgsql/data/pg_hba.conf # change the method from say md5 or ident to trust # save by pressing CTRL + O, CTRL + X # restart postgres /etc/rc.d/init.d/postgresql restart # login su - postgres psql alter user user_name with password 'thePass'; # restore your changes to .conf nano /var/lib/pgsql/data/pg_hba.conf # restart again /etc/rc.d/init/d/postgresql restart # done!