Messing Around with PostgreSQL

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.

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 - postgres
# it don't ask password anymore

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.

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
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!

Leave a reply

Your email address will not be published. Required fields are marked *