Motaz
asked on
PostgreSQL problem
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
I tried to remove Postgresql and install it again, but with no luck, the same error I get
Please help
Motaz
ASKER
I don't care about data, but where can I find the old data directory?
Motaz
Motaz
ASKER
btw. is there any windows version of Postgresql server?
Motaz
Motaz
> 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
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
ASKER
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
I'm Linux beginner, so that I hope that I can defrentian between Is -IR, is-iR, or may be 1s-1R:)
Motaz
ASKER
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
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
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
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
ASKER
Thanks alot Galenn or Ga1enn, or GaIenn:)
I'm going now home, I was waiting your last answer.
Motaz
I'm going now home, I was waiting your last answer.
Motaz
ASKER
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
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
ASKER
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
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
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-5m
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
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-5m
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
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
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
ASKER
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
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
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</
" <td><font face=\"Verdana\">$hostname
" <td><font face=\"Verdana\">$sys_own<
" <td><font face=\"Verdana\">$ipstack<
" <td><font face=\"Verdana\">$comment<
" </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
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
>... 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</
" <td><font face=\"Verdana\">$hostname
" <td><font face=\"Verdana\">$sys_own<
" <td><font face=\"Verdana\">$ipstack<
" <td><font face=\"Verdana\">$comment<
" </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
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
ASKER
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
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
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
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
ASKER
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
[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
ASKER
I used phpinfo(), but I cann´t determine wither postgres is enabled or not, in which secion and which variable?
Motaz
Motaz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Motaz from Sudan
www.geocities.com/motaz1
www.geocities.com/motaz1
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:-)
-- Glenn (in Stockholm... A whole lot cooler than Khartoum, I'd bet! I'm not quite 40, but very near:-)
ASKER
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
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
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
Still no go?
-- Glenn
ASKER
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
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
ASKER
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
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
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
> 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
psql -d ipadresser -U it
.... logged on as "any user". And it should just work, provided you enter a correct password.
OK?
-- Glenn
> 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
> 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
psql -d ipadresser -U it
.... logged on as "any user". And it should just work, provided you enter a correct password.
OK?
-- Glenn
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