Solved

PostgreSQL problem

Posted on 2003-11-18
27
1,341 Views
Last Modified: 2013-12-06
I have Red Hat 8 installed in my computer, then I upgraded to Red Hat 9, but I cann't make Postgresql service working, it tells me that there is an old format of the database, please upgrade the format...
I tried to remove Postgresql and install it again, but with no luck, the same error I get

Please help
Motaz
0
Comment
Question by:Motaz
  • 16
  • 11
27 Comments
 
LVL 20

Expert Comment

by:Gns
ID: 9770087
That's because you keep the postgres database even when you remove the rpm package (this is sane... you wouldn't want to loose the data just on account of a "simple" upgrade:-).
One is supposed to run a pg_dumpall (and redirect that to a file) prior to the rpm update, and then restore from that script.
look at what you have...
ls -l ~postgres/backups
ls -lR ~postgres/data
(or whatever... I run my postgresqls on mandrake these days:-)
In the backups directory you might have the old version of the dump commands, so that you can actually generate a script file... ISTR that RH usually have a rh-pgdump script that will try help you update the database files (mandrake does), but I also seem to recall that that script usually doesn't work:-)... Been a while so things might be different...
If you don't care about the data, you could simply remove the rpm packages, and then manually remove the "residual" directories from the postres user... and then reinstall the packages.

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9770173
I don't care about data, but where can I find the old data directory?

Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 9770218
btw. is there any windows version of Postgresql server?

Motaz
0
 
LVL 20

Expert Comment

by:Gns
ID: 9770330
> I don't care about data, but where can I find the old data directory?
When you've rmp -e'd all the postrgesql packages, you'll find them "somewhere under" ~postrgres ... just
cd ~postgres
ls -lR
and you should see them... in the data subdir.
Just remove 'em all with
rm -rf *

> btw. is there any windows version of Postgresql server?

I'm into linux... but let's see... hmmm, the only option I found with a quick peek at http://www.postrgresql.org (and has actually tried out some time back in ancient forgetfuldom) is to run it under cygwin (http://www.cygwin.com ... DL and run setup.exe for the install), which is ... "unixyness for windows":-).

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9770369
Ok, I'll try it after I go home, and I'll reply to u tomorrow in sha allah.
I'm Linux beginner, so that I hope that I can defrentian between Is -IR, is-iR,  or may be 1s-1R:)

Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 9770378
btw. My friend remove the packages using :

rpm -U postgresql
or something like this, but I tried it my self but it is not working, so how to remove it, and how can I list all installed packages?
I tried rpm --all, but it is not workig

Motaz
0
 
LVL 20

Expert Comment

by:Gns
ID: 9770463
Well... using a well defined font for your browser should help with the differentiating:-).

rpm -qa|grep postgres
to list all packages to do with postresql.
then
rpm -e <all the packagenames from the above command... at least those that don't look like a "red herring";)>

The "rpm -U" command is actually "upgrade to packagecontained in the filename, or install it"... rpm -F is "freshen" which is to say "update only if already installed".

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9770476
Thanks alot Galenn or Ga1enn, or GaIenn:)

I'm going now home, I was waiting your last answer.

Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 9776999
I tried to unistall postgresql-server, but it hangs, (about 30 minutes without response)
and I cann't find database directory, so that I delete most of directories:

/etc
/var
/tmp

then I installed Red Hat 9 again,
when I run postgresql from KDE, it gives me: "postgresql" start successfully, but when I show it's status it gives me "postmaster stopped"

when I type: "service postgresql start" it gives me "failed", without the reason

Please Help
Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 9777000
I tried to unistall postgresql-server, but it hangs, (about 30 minutes without response)
and I cann't find database directory, so that I delete most of directories:

/etc
/var
/tmp

then I installed Red Hat 9 again,
when I run postgresql from KDE, it gives me: "postgresql" start successfully, but when I show it's status it gives me "postmaster stopped"

when I type: "service postgresql start" it gives me "failed", without the reason

Please Help, I don't want to use MySql
Motaz
0
 
LVL 20

Expert Comment

by:Gns
ID: 9778090
Whoa... that was taking it a bit far (deleting your main configs in /etc etc:-)... The postgres user usually have /var/lib/pgsql as home directory After doing that, reinstalling would be the prudent thing;-). Did you do a clean install, or "repaired it" with an upgrade (rh9->rh9:)? probably a clean install.

OK, so now we are with a "clean slate" as far as postgresql is concerned. Hmmm, I have a vague memory of something about this... Lets see.
On a Mandrake 9.1 "near me" (postgresql version 7.3.2), the command to start the postmaster (the main postgresql server process) is something like
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start  > /dev/null 2>&1" < /dev/null
with the variables set as:
PGPORT=5432
PGDATA=/var/lib/pgsql
(... and I've got the following installed
# rpm -qa|egrep postgres
postgresql-server-7.3.2-5mdk
postgresql-7.3.2-5mdk
...)
If you skip the redirections at the end, you'd probably see a more "verbatim reason" for the failure...
# su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D /var/lib/pgsql -p /usr/bin/postmaster -o '-p 5432' start

You can use the service command to check that it hasn't already been started (the script should "see" this):
service postgresql status

Hm, I think I'll find time to install this on a RH9 "near me" instead, perhaps it's something simple... I have a vague recollection that it would be:-).

I'll check and get back within the hour.

-- Glenn
0
 
LVL 20

Expert Comment

by:Gns
ID: 9778294
Ok, I've installed (via the "Add/remove prgms horror":-) postgresql... and it "Just Works"...
After the install, ~postgres/data (and ~postgres/backups) is empty, so when I did
service postgresql start
a new database was initialized.
after that, sunning
service postgresql restart
(which in turn just does the stop method followed by the start method) works OK.
To make it start at bootup, do
chkconfig postgresql on
and you should be fine at the next reboot (check with "chkconfig --list postgresql" to see what runlevels it'll run at).

If something went bonkers with the database creation/initialization (it creates the template1 database), you can just
service postgresql stop
rm -rf ~postgres/data/*
service postgresql start
... and things should be OK again.

In all the above examples I assume you execute the commands from a command prompt, logged on as root ("su - root" from your regular user perhaps).
I've also verified that RH9 and Mdk9.1 use the same PostgreSQL version (and init-script), so the mandrake examples are exactly the same as with redhat.
Good luck

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9778358
In my work computer, it is working fine. I mean if I use service postgresql status, it tells me that it is running, actually I don't know what is the next step (it can be in another question:)

After I delete that folders, RH installer seems to do a clear installation, but I get my files and applications (Kylix) running after that installation.

Please tell me if the solution for this is to install a very clean copy of RH9 tell me, I don't want to wait more days to install postgres, I want to keep another days to write my first SQL statements on it, then the rest of month, to link it with PHP, then my rest of life to make it accessible in the network:)

Motaz
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 20

Expert Comment

by:Gns
ID: 9779918
Okay... lets see....
>... I mean if I use service postgresql status, it tells me that it is running ...
This means that postgres is running.

You can now become the postgres user and start running psql (looks a bit like mysql interpreter... or sqlplus of Oracle fame:-). You can then create databases, "database users" etc...\? and \h are your friends here, as well as the man pages ("man create_database" ...).
The only database you'd have would be the template1 database, and the default postgres user...

Examples (snipped/edited from a pg_dumpall):
CREATE USER it WITH SYSID 100 PASSWORD 'whatever' CREATEDB CREATEUSER;
CREATE DATABASE ipadresser WITH OWNER = it TEMPLATE = template0 ENCODING = 'SQL_ASCII';
CREATE TABLE adresser (
    adress inet NOT NULL,
    hostname character varying(32),
    sys_own character varying(64),
    ipstack character varying(64),
    "comment" text
);
COPY adresser (adress, hostname, sys_own, ipstack, "comment") FROM stdin;
10.0.0.10/8  Switch-450_DH   Switch BayStack 450     Bay Network
\.

The above should be fairly easy to understand what it does. You should then be able to (as any local user) connect with
psql -d ipadresser -U it
# SELECT * FROM adresser order by adress;

... or from a php script do
$conn = pg_connect ("dbname=ipadresser user=it password=whatever");
if (!$conn) {
  echo "An error occured when connecting.\n";
  exit;
}

$result = pg_Exec ($conn, "set datestyle to 'ISO'");
if (!$result) {
    echo "An error occured when executing.\n";
    exit;
}

$result = pg_Exec ($conn, "SELECT * FROM adresser order by adress");
if (!$result) {
    echo "An error occured when executing.\n";
    exit;
}
$ntuples = pg_numrows($result);
$i = 0;
while (list($adress, $hostname, $sys_own, $ipstack, $comment) = pg_fetch_row($result,$i)) {
    print("    <tr>\n");
    print("     <td><font face=\"Verdana\">$adress</font></td>\n".
          "     <td><font face=\"Verdana\">$hostname</font></td>\n".
          "     <td><font face=\"Verdana\">$sys_own</font></td>\n".
          "     <td><font face=\"Verdana\">$ipstack</font></td>\n".
          "     <td><font face=\"Verdana\">$comment</font></td>\n".
          "    </tr>\n");
    $i++;
    if($ntuples <= $i) break;
}
pg_close($conn);
.... or whatever you'd like to do:-).
Connecting from kylix should be trivially similar, but I've been out of touch with borlands IDE/RADs for a while, so cannot help with specifics.

By default the postmaster will not listen for network connections (for security), but it is easy enough to enable... just edit ~postgres/data/pg_hba.conf and enable it for a reasonable set of hosts/users/subnets.
Note that the default setting for "local" isn't that great... It would prevent web "users" from accessing the database via php. I use:
local  all     all                                                  trust
instead of
#org#local  all     all     ident   sameuser
This trust effectively disables the need for password protection (or at least makes it like a "don't care"), so only use in secure environments:-).
You need to
service postgresql restart
to make the new settings take effect.

Or did I miss your question here? If the postmaster is up and running, you are more or less "set to go"!:-)

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9785306
Hello Glenn,
finally it works in home!
I install linux again, and I use format this time (Now I'm sure there is no old version of postgres database, unless it is hidden in my other windows partition:)

Things that I can do:

- I can create database
- Create tables
- Insert data

Things that still cann't do:

- create user (if I use CREATE USER command it tells me CREATE not found)
- in php, it tells me: Fatal error: Call to undefined function: pg_pconnect() in /var/www/html/post.php on line 2

Please help, I don't want to stick only with Micro$oft
Motaz

0
 
LVL 20

Expert Comment

by:Gns
ID: 9785773
Do you have the php-pgsql package installed? Check what php things you've got with
rpm -qa | grep php
or perhaps
grep php /var/log/rpmpkgs

As I'm sure you know, you can create the minimal php script (lets say you put it in info.php)
<?phpinfo()?>
that will tell what "modules&stuff" php thinks it has. Very nice for checking that it finds its postgresql bindings...
.... and note that I use pg_connect(), not pg_pconnect() ...

You should be able to create a user in psql (I just did, to confirm:-)... Check what users you have with
select * from pg_user;
(pg_user is a view... Look at what "system tables" you have with \dS). The sysid has to be unique.
Remember that you have some nice features in psql... like readline support (so you can use "normal shell command history editing"... like arrow keys) and "command completion (pressing <TAB> will "complete" the SQL command as far as is possible... CR<TAB> -> CREATE ... A second <TAB> will list possible completions. Unfortunately it only works for the "initial command" part, so from WITH and onwards you'll have to be your own spelling corrector:-).

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9786765
This is the packages that I have:

[root@shq_it_prg_02 root]# rpm -qa|grep php
php-ldap-4.2.2-17
php-imap-4.2.2-17
php-4.2.2-17

[root@shq_it_prg_02 root]# grep php /var/log/rpmpkgs
php-4.2.2-17.i386.rpm
php-imap-4.2.2-17.i386.rpm
php-ldap-4.2.2-17.i386.rpm
0
 
LVL 7

Author Comment

by:Motaz
ID: 9786817
I used phpinfo(), but I cann´t determine wither postgres is enabled or not, in which secion and which variable?

Motaz
0
 
LVL 20

Accepted Solution

by:
Gns earned 250 total points
ID: 9786960
You don't have it installed... It is on installation CD2. Put that into your drive, and either
a) let it "automagically" open nautilus/konqueror... it'll open /mnt/cdrom... go to RedHat->RPMS and click/double-click on php-pgsql-4.2.2-17.i386.rpm
or
b) mount /mnt/cdrom
rpm -ivh /mnt/crom/RedHat/RPMS/php-pgsql-4.2.2-17.i386.rpm
umount /mnt/cdrom

and you should be fine;).

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9787320
Ok, I'll try it at home, because I have no CDs here,
any way you helped me alot Glenn, and you are very patented, it seems that you are above 40 years old. I hope that you follow the same way with all beginners like me.

If I have more points I could give you, you deserve more, thanks alot and keep helping people

Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 9787327
Motaz from Sudan
www.geocities.com/motaz1
0
 
LVL 20

Expert Comment

by:Gns
ID: 9787624
Ah, thanks Motaz... The satisfaction of really helping someone... real reason most experts do what we do at EE:-). It's a bit like a drug...;-)

-- Glenn (in Stockholm... A whole lot cooler than Khartoum, I'd bet! I'm not quite 40, but very near:-)
0
 
LVL 7

Author Comment

by:Motaz
ID: 9802120
Glenn, I found that php-pgsql package installed in my home computer, but I get another error:

pg_connect('dbname=test user=it password=whatever');
I get : Authentication failed for user it

and I tested:

pg_connect('dbname=test user=postgres');
I get the same error

Motaz
0
 
LVL 20

Expert Comment

by:Gns
ID: 9809505
Check that you don't have "ident sameuser" set in pg_hba.conf (rather set "trust" as in my above example), and restart the service.
Still no go?

-- Glenn
0
 
LVL 7

Author Comment

by:Motaz
ID: 9817798
I changed this line in httpd.conf:

User apache

to

User postgres

and it works, but I'm afraid when I deploy my application in real server, I may not able to do that, so that can I make the configuration in my connection or database site?
What did u mean by Trust?

Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 9817800
I changed this line in httpd.conf:

User apache

to

User postgres

and it works, but I'm afraid when I deploy my application in real server, I may not able to do that, so that can I make the configuration in my connection or database site?
What did u mean by Trust?

Motaz
0
 
LVL 20

Expert Comment

by:Gns
ID: 9823390
I'll comment myself here:-)

> By default the postmaster will not listen for network connections (for security), but it is easy enough to enable... just edit ~postgres/data/pg_hba.conf and enable
> it for a reasonable set of hosts/users/subnets.
> Note that the default setting for "local" isn't that great... It would prevent web "users" from accessing the database via php. I use:
> local  all     all                                                  trust
> instead of
> #org#local  all     all     ident   sameuser
> This trust effectively disables the need for password protection (or at least makes it like a "don't care"), so only use in secure environments:-).
> You need to
> service postgresql restart
> to make the new settings take effect

So ... the pstgresql is running/owned by postgres _PG-USER_... Changing httpd.conf so that Apache runs as postgres insted of apache just make "things add up in the wrong end".
The above pg_hba.conf entry is OK in situations where you have control over the server, and don't listen to any network... As I have it:-). I'm guessing you'll have a slightly more "complex" situation, where it would be better to actually use the database password set... So change the local line to read
local all all md5
in /var/lib/pgsql/data/pg_hba.conf and you should be fine (after a "service postgresql restart")... After that you should be prompted for a password when you do
psql -d ipadresser -U it
.... logged on as "any user". And it should just work, provided you enter a correct password.
OK?

-- Glenn
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
In my business, I use the LTS (Long Term Support) versions of Linux. My workstations do real work, and so I rarely have the patience to deal with silly problems caused by an upgraded kernel that had experimental software on it to begin with from a r…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now