PostgreSQL is a powerful, open source relational database system with active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation. For more details and features, click here
To install in openSUSE 11.0
opensuse:~ # yast2 –install postgresql-server
This installs postgreSQL Database server on your openSUSE system.
The files for postgreSQL are installed in
/usr/share/postgresql/
Here you can find the sample files.
Actual data and runtime files are found here
/var/lib/pgsql/
The config files are found in
/var/lib/pgsql/data
The files of importance are pg_hba.conf & postgresql.conf
Once installation is complete, start postgreSQl for the first time
opensuse:~ # rcpostgresql start
Set Username & password
Login as postgres user and set the password
opensuse:~ # su postgres -c psql postgres
From the postgresql prompt
postgres=# ALTER USER postgres WITH PASSWORD ‘postgres’;
ALTER ROLE
Quit from postgreSql prompt
postgres=# \q
One of the tings that needs to be checked is whether you use IDENT to authenticate users. If you don’t then you need to edit and modify the lines that allow user access to not to use IDENT. Else, you may face with error
Error!
“IDENT authentication failed for user”
Hence, if you do not use IDENT, edit the /var/lib/pgsql/data/pg_hba.conf file and change “ident” to “md5″. Say, for instance, the lines below
local all all ident sameuser
host all all 127.0.0.1/32 ident sameuser
host all all ::1/128 ident sameuser
to
local all all md5 sameuser
host all all 127.0.0.1/32 md5 sameuser
host all all ::1/128 md5 sameuser
Restart postgreSQL
Once done,restart postgreSQL as follows:
opensuse:~ # rcpostgresql restart
Create and Delete users
To create an user
opensuse:~ # su postgres
postgresql@opensuse:~> createuser -D <username>
for user with password
postgresql@opensuse:~> createuser -D -p <username>
To delete user
postgresql@opensuse:~> dropuser <username>
Thanks for a fantatic post. Quick and to the point.
One question though…. how does one get pgAdmin to work on openSuse 11? Any luck on that one?
Kind regards,
Jacobus
You asked for it and here it is…
http://www.susegeek.com/database/how-to-install-and-configure-pgadmin-iii-for-postgresql-in-opensuse/
Excellent post,
All procedure is to the point and very clear.Thanks for it.These procedures is same for cenos except some form of command.
I am new with linux. I had installed postgresql during the installation of opensuse 11. I wonder, which password opensuse did set for the postgres – user. I could change the password via yast2 and go through this tutorial successfully. (Thanks for that post!) But I wonder which password opensuse uses for system-users like postgresql. It was not ‘postgresql’, ‘posgtgres’ etc and not my root-password.
Hej,
I used your well written instruction to install postgresql and am now trying to install pgadmin III as well. I did not realize that there is a slightly different way during the installation of postgresql when you intend to do so. I not this “normal” installation works as well?!
Here is my actual question:
runtime file are supposed to be found under /var/lib/pgsql. but there is now ‘bin’ folder or something that would look like runtime files to me – a linux newbie:
-rw——- 1 postgres postgres 42 Aug 3 15:23 .bash_history
-rw-r—– 1 postgres postgres 192 Jun 7 00:46 .bash_profile
-rw——- 1 postgres postgres 49 Aug 2 11:07 .psql_history
drwx—— 2 postgres postgres 4.0K Jun 7 00:46 backups
drwx—— 11 postgres postgres 4.0K Aug 3 14:51 data
-rw-r–r– 1 postgres postgres 1.3K Aug 2 10:51 initlog
initlog says everything is fine though…
BUT ./configure for pgadmin complains that now postgres installation can be found.
Using ./configure –with-pgsql=/var/lib/pgsql does not solve this.
Hope it’s OK to ask for a kind of direct support – any help is appreciated.
Axel
Have you tried my installation procedure here?
http://www.susegeek.com/database/how-to-install-and-configure-pgadmin-iii-for-postgresql-in-opensuse/
Hej,
as I did not want to mix two installations of postgresql, I did not try to follow this instruction. So I can just follow it – or shall I remove stuff before I start anew?
Moreover, I still wonder if files are missing in my current /var/lib/pgsql directory…
TIA
Axel
I would kiss you when you sitting next to me this IDENT shit has driven me crazy
Wow… perfect tutorial to configure postgres. You made my day! Thanks. I would kiss u for sure if u were sitting next to me
Autostart Issue:
I’ve always used this procedure to have postgres autostart on reboots.
Procedure I’ve used during set-up:
– Copy file linux from /root/postgres…/contrib/start-scripts to /etc/rc.d/
– Rename file from linux to postgresql
– Log in is root
– chmod 744 /etc/init.d/postgresql
– insserv /etc/init.d/postgresql
– Reboot system, Postgres should start automatically
With opensuse 11 I get an error when I use the insserv command
insserv: warning: script ‘postgresql’ missing LSB tags and overrides
Any idea how to get uuid to install and work? In the past I’d configure postgres ./configure –with-perl –with-ossp-uuid
I used the openSuse installation of postgres and didn’t get to option to configure it the way I wanted to.
I’m also running the 64bit version of openSuse.
Any help would be great!…I’m not a linux guy.
System:
* openSuse 11 64bit (Installed with postgres during set-up)
* uuid-1.6.2.tar.gz into /usr/share/postgresql/contrib/uuid-ossp/
o tar xvzf uuid….
o cd uuid…
o ./configure –with-pgsql
o make
o make install
At this point everything is successful.
Now I add this:
/usr/bin/psql -d postgres -U postgres -f /usr/share/postgresql/contrib/uuid-ossp/uuid-1.6.2/pgsql/uuid.sql
Basically it fails.
When I look at the script the functions are already in postgres when I look through it in pgAdmin.
So I’m assuming I can skip that part??
Now for the fun part……
I’m a Windows guy and for me to get this to work was easy. In the ../share/contib folder I ran the file uuid-ossp.sql file and ran the script. All the uuid functions work perfectly!
They have functions written in there like this:
— Adjust this setting to control where the objects get created.
SET search_path = public;
CREATE OR REPLACE FUNCTION uuid_nil()
RETURNS uuid
AS ‘$libdir/uuid-ossp’, ‘uuid_nil’
IMMUTABLE STRICT LANGUAGE C;
I try running the same script and:
ERROR: could not access file “$libdir/uuid-ossp”: No such file or directory
Of course this script was for a windows box and I’m sure the linux version will look a little different.
Got any ideas?
On my install of SuSE 11.x, password is an uppercase P not lower as you have here:
postgresql@opensuse:~> createuser -D -p
Hi ,I installed postgresql from yast2,but didn’t get pg_hba.conf & postgresql.conf files in /var/lib/pgsql/data
Any luck on this
you need run it on pgsql 9 or later
/e/etc/rc.d/init.d/postgresql-9.0 initdb -E UTF8
on pgsql 8.XX
/e/etc/rc.d/init.d/postgresql initdb -E UTF8
As stated earlier:
Roland Says:
November 12th, 2008 at 7:06 pm
On my install of SuSE 11.x, password is an uppercase P not lower as you have here:
postgresql@opensuse:~> createuser -D -p [username]
Please correct this so newbies to Postgres don’t spin their wheels with a Postgres connection error that results from an invalid port number, provided above as [username].
Please note the mistake in your install suggestion – it should read :-
yast2 –-install postgresql-server
and not yast2 –install postgresql-server
It took me a while to figure this out as I’m new to SuSE