Install and Configure PostgreSQL in openSUSE 11.0
Posted by admin on July 2nd, 2008
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>
July 7th, 2008 at 5:51 am
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
July 8th, 2008 at 1:57 pm
You asked for it and here it is…
http://www.susegeek.com/database/how-to-install-and-configure-pgadmin-iii-for-postgresql-in-opensuse/
July 17th, 2008 at 3:11 am
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.
July 22nd, 2008 at 3:45 am
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.
August 3rd, 2008 at 8:53 am
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
August 3rd, 2008 at 10:24 am
Have you tried my installation procedure here?
http://www.susegeek.com/database/how-to-install-and-configure-pgadmin-iii-for-postgresql-in-opensuse/
August 4th, 2008 at 4:08 am
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
October 20th, 2008 at 10:29 am
I would kiss you when you sitting next to me this IDENT shit has driven me crazy
October 21st, 2008 at 6:30 am
Wow… perfect tutorial to configure postgres. You made my day! Thanks. I would kiss u for sure if u were sitting next to me
November 5th, 2008 at 9:33 pm
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
November 9th, 2008 at 8:07 am
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.
November 9th, 2008 at 12:38 pm
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?
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
November 21st, 2008 at 10:18 am
[...] user “system”. Długo się zastanawiałem o co chodziło z tym słówkiem IDENT, aż trafiłem na wpis w blogu który mnie oświecił Okazało się, że domyślnym sposobem autoryzacji jest IDENT, co oznacza [...]
October 19th, 2009 at 8:24 pm
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
June 25th, 2010 at 9:20 am
[...] [...]
December 1st, 2010 at 1:02 pm
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
February 21st, 2011 at 6:23 pm
[...] correctly ]. And hence lastly I goggled and found something relevant and useful at this page : http://www.susegeek.com/database/ins…-opensuse-110/ all the commands on this page [ i didn't tried all commands after : "postgres=# q". [...]
May 11th, 2011 at 8:06 am
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].
September 10th, 2012 at 5:57 am
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