Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 19:37:43 Adrian Klaver wrote: > On 04/10/2017 01:23 PM, John Iliffe wrote: > > On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > >>John Iliffe wrote: > >>> So, the problem is resolved, although I have no idea why it was > >>> necessary. > >> > >> The key seems to be the PrivateTmp=true in the systemd service. > >> Apache is not chrooted as demonstrated upthread, but that > >> setting alone makes the normal, system-wide /tmp inaccessible > >> to its processes, including the postgres Unix domain socket opened > >> there. > >> > >> I suspect that your attempt to set PrivateTmp=false as a test was > >> missed by systemd for some reason. You could probably insist > >> in that direction and eventually make it work, but I don't see > >> how it's better than the alternative /var/pgsql or localhost through > >> TCP. > >> > >>> Also, I now have several hundred programmes to update to add the > >>> host path and none of them will now be portable. > >> > >> Given that you set two directories: /tmp and /var/pgsql, > >> I would think you can let the other apps use /tmp as before > >> and have only Apache use /var/pgsql ? > > > > Yes, I will do that, but there are several hundred PHP web page > > scripts to be updated. Presumably if one script opens two different > > databases then both of the pg_connect() instances will need to be > > updated. > > Out of curiosity where did you install Postgres from? > > The reason I ask it that I was trying to figure why Fedora would invoke > private /tmp's for services and then have the Postgres socket be only in > the public /tmp. So I spun up a Fedora 25 instance and did an install of > Postgres from the Fedora repos and then from the PGDG repos. In either > case I got a postgresql.conf that had: > > unix_socket_directories = '/var/run/postgresql', '/tmp' > > Upstream you showed your default as: > > unix_socket_directories = '/tmp' > > which is what I see when I do a source install. > > So did you do a source install or did you copy a postgresql.conf from > somewhere else? I downloaded the source from https://www.postgresql.org/ftp/source/v9.6.2/ and installed according ot the notes included there. > > >> Best regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Thanks for the idea Rick. It hadn't occurred to me; might have at about the 200th page :-( John = On Monday 10 April 2017 16:46:14 Rick Widmer wrote: > On 4/10/2017 2:23 PM, John Iliffe wrote: > > On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > >>John Iliffe wrote: > >> Given that you set two directories: /tmp and /var/pgsql, > >> I would think you can let the other apps use /tmp as before > >> and have only Apache use /var/pgsql ? > > > > Yes, I will do that, but there are several hundred PHP web page > > scripts to be updated. Presumably if one script opens two different > > databases then both of the pg_connect() instances will need to be > > updated. > > How about creating a php file with the connect code, once, then > including that file from all of the hundreds of web pages. It won't > help much with the first set of changes, but it will help with > portability later. > > > Rick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > John Iliffe wrote: > > So, the problem is resolved, although I have no idea why it was > > necessary. > > The key seems to be the PrivateTmp=true in the systemd service. > Apache is not chrooted as demonstrated upthread, but that > setting alone makes the normal, system-wide /tmp inaccessible > to its processes, including the postgres Unix domain socket opened > there. > > I suspect that your attempt to set PrivateTmp=false as a test was > missed by systemd for some reason. You could probably insist > in that direction and eventually make it work, but I don't see > how it's better than the alternative /var/pgsql or localhost through > TCP. > > > Also, I now have several hundred programmes to update to add the host > > path and none of them will now be portable. > > Given that you set two directories: /tmp and /var/pgsql, > I would think you can let the other apps use /tmp as before > and have only Apache use /var/pgsql ? Yes, I will do that, but there are several hundred PHP web page scripts to be updated. Presumably if one script opens two different databases then both of the pg_connect() instances will need to be updated. > > > Best regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 09:46:54 Daniel Verite wrote:
> John Iliffe wrote:
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there?
>
> pg_connect("host=/var/pgsql [...other parameters...]")
>
> The fact that the value for host starts with a slash indicates
> without ambiguity that it's a path on disk rather than the name
> or address of a host on the network.
>
YUP IT WORKS!
to borrow Apache's happy-message when you first install it.
So, the problem is resolved, although I have no idea why it was necessary.
Also, I now have several hundred programmes to update to add the host path
and none of them will now be portable.
Somehow this seems a step backwards, but my sincere thanks to everyone on
this list who helped me. The whole thing was way beyond my detailed
understanding of Linux.
I provided some further information in a previous memo about the details of
the unit file which may provide some information for those who understand
such things. It might be a good idea to provide some details of the
systemctl process in the Postgresql documentation for people who are first
encountering it (like me) when they have been used to the old init.d file
approach.
I will bring the same thing to Apache's attention on their list too.
Regards,
John
> However if apache runs in a chroot jail, it should no more see
> /var/pgsql than it sees /tmp
> Given an apache process id, on Linux you should be able
> to check the current filesystem-root of that process with
> # ls -ld /proc/$PID/root
> It should show a soft link to the directory that corresponds to
> the root from the point of view of the $PID process.
>
> But again, most people would use host=localhost in this setup.
>
> Best regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Monday 10 April 2017 09:46:54 Daniel Verite wrote:
> John Iliffe wrote:
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there?
>
> pg_connect("host=/var/pgsql [...other parameters...]")
>
> The fact that the value for host starts with a slash indicates
> without ambiguity that it's a path on disk rather than the name
> or address of a host on the network.
>
> However if apache runs in a chroot jail, it should no more see
> /var/pgsql than it sees /tmp
> Given an apache process id, on Linux you should be able
> to check the current filesystem-root of that process with
> # ls -ld /proc/$PID/root
> It should show a soft link to the directory that corresponds to
> the root from the point of view of the $PID process.
>
You are exceeding my level of understanding of Linux but the following
result suggests to me that Apache is using the common root directory (/) as
its base.
-
[root@prod04 John]# ls -ld /proc/27753/root
lrwxrwxrwx. 1 root root 0 Apr 10 10:52 /proc/27753/root -> /
--
Other than the rather odd requirement in the systemctl unit file that httpd
has a private /tmp (see arrow below) there should be no reason why Apache
can't see the /tmp directory. One of the earlier references in this series
mentioned I should check for that, which is the reason for the secondary
socket on /var/pgsql/ . I don't know how to make mod_php, or for that
matter php-fpm, look for the Postgresql socket on /var/pgsql/ though.
There is no conf file for mod_php and no directive that I can find in the
php-fpm configuration file.
[root@prod04 John]# cat /etc/systemd/system/httpd.service
[Unit]
Description=The Apache HTTP Server
[Service]
Type=forking
EnvironmentFile=/usr/apache-2.4.25/bin/envvars
PIDFile=/var/run/httpd.pid
ExecStart=/usr/apache-2.4.25/bin/apachectl -k start
ExecReload=/usr/apache-2.4.25/bin/apachectl -k graceful
ExecStop=/usr/apache-2.4.25/bin/apachectl -k stop
KillSignal=SIGCONT
PrivateTmp=true<---
[Install]
WantedBy=multi-user.target
FYI, changing the PrivateTmp directive to false doesn't fix the problem and
since I don't know why it is there I don't ant to touch it.
> But again, most people would use host=localhost in this setup.
>
> Best regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 23:21:58 Adrian Klaver wrote: > On 04/09/2017 05:30 PM, John Iliffe wrote: > > On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote: > >> So the issue is in PHP via Apache using the socket, because if I > >> remember right you used localhost in the Apache/PHP combination and > >> it worked, correct? > > > > Yes. > > > > I think there is some confusion here, might be on my part, I don't > > know. > > > > There is a network connection from 192.168.1.10 to 192.168.1.6 to > > Apache and then there should be a connection from Apache on using > > localhost (or 127.0.0.1) to Postgresql. So shouldn't that be > > sufficient? Other than the original error on my part, coding the > > server's external address (192.168.1.6) in the db_connect() call > > which is now fixed, shouldn't the pg_hba host address line be > > 127.0.0.1/32 ? > > Yes, Apache is connecting to Postgres on the same machine so localhost > should be sufficient for IP purposes. > > Not sure that it applies here, but what does ifconfig show? > --- root@prod04 John]# ifconfig -a enp0s20f0u10: flags=4163 mtu 1500 inet 192.168.1.7 netmask 255.255.255.0 broadcast 192.168.1.255 inet6 fe80::84a:4eb9:f4a4:98a6 prefixlen 64 scopeid 0x20 ether 54:b8:0a:ef:c7:0f txqueuelen 1000 (Ethernet) RX packets 210126 bytes 238984885 (227.9 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 120884 bytes 14119037 (13.4 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 enp0s31f6: flags=4163 mtu 1500 inet 192.168.1.6 netmask 255.255.255.255 broadcast 192.168.1.6 inet6 fe80::62d4:f478:8bbb:34a1 prefixlen 64 scopeid 0x20 ether 38:d5:47:19:0d:cb txqueuelen 1000 (Ethernet) RX packets 4589 bytes 1072227 (1.0 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 1003 bytes 71483 (69.8 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 device interrupt 16 memory 0xf700-f702 lo: flags=73 mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10 loop txqueuelen 1000 (Local Loopback) RX packets 106993 bytes 22736948 (21.6 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 106993 bytes 22736948 (21.6 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 --- The actual connection being used should be on device "lo" which is on the "localhost" address of 127.0.0.1. > > Anyhow, that is working properly now. The domain socket doesn't have > > an explicit address (for 'local') as it is on the current machine as > > far as I understand. > > > > Am I correct? > > Correct. The issue is then why psql and stand alone PHP can see the > domain socket while PHP through Apache does not. Not sure why that is at > the moment. Hmm, had a thought. What user is Apache running as and does > that user have permissions on the socket file(s)? > Looks like anybody can connect to either of the Postgresql domain sockets. socket on /tmp srwxrwxrwx. 1 postgres postgres system_u:object_r:initrc_tmp_t:s00 Apr 10 10:35 .s.PGSQL.5432 second socket on /var/pgsql -- srwxrwxrwx. 1 postgres postgres system_u:object_r:httpd_var_run_t:s0 0 Apr 10 10:35 .s.PGSQL.5432 -- Apache is running under userid apache as expected --- [root@prod04 John]# ps -ef | grep httpd root 420 356 0 10:39 pts/000:00:00 grep --color=auto httpd root 27753 1 0 Apr09 ?00:00:00 /usr/apache-2.4.25/bin/httpd -k start apache 27754 27753 0 Apr09 ?00:00:03 /usr/apache-2.4.25/bin/httpd -k start apache 27755 27753 0 Apr09 ?00:00:04 /usr/apache-2.4.25/bin/httpd -k start apache 27756 27753 0 Apr09 ?00:00:04 /usr/apache-2.4.25/bin/httpd -k start with the following SELinux context info: --- ps -eZ | grep httpd system_u:system_r:unconfined_service_t:s0 27753 ? 00:00:00 httpd system_u:system_r:unconfined_service_t:s0 27754 ? 00:00:03 httpd system_u:system_r:unconfined_service_t:s0 27755 ? 00:00:04 httpd system_u:system_r:unconfined_service_t:s0 27756 ? 00:00:04 httpd -- > > John > > > >>>> Report back. > >>>> > >>>>> Based on the reference that Joe sent earlier, I do have a second > >>>
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 22:07:12 rob stone wrote: > On Sun, 2017-04-09 at 20:09 -0400, John Iliffe wrote: > > > > > > > > > You have Apache, PHP, and Postgres all running on your LAN at > > > 192.168.1.6. > > > > > > > > > > > > > > > You are on 192.168.1.10. > > > > > > > > > > > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to > > > > run > > > > > that script? > > > > testfcgi.php is in the document root of the default named virtual > > server. > > O.K. > > So in sites-available, your conf file just tells Apache to run > testfcgi.php and nothing else? Apache just runs the programme whose name follows the / in the URL. Nothing fancy here at all. testfcgi.php is a php script that is run by mod_php in Apache and uses the php command pg_connect() to try and reach Postgresql. That works if Postgresql is reached via a TCP connection, as suggested by Adrian yesterday, but not if the connection is using a Unix domain socket, which is the usual way to do that. Despite the name, testfcgi.php is running as a mod_php script, not a cgi. That is the next step to get working! Regards, John > There is no resolution required by obtaining the IP address from > /etc/hosts. > > Are you able to put some trigger_error messages into testfcgi.php in > order to make sure Apache is running the correct program? > > Cheers, > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 20:07:01 Adrian Klaver wrote: > On 04/09/2017 03:27 PM, rob stone wrote: > > Hello John, > > > > Just saw this message. > > > >> Still set to the default: > >> > >> #listen_addresses = 'localhost' # what IP address(es) to > >> listen on; > >> > >> # comma-separated list of > >> > >> addresses; > >> > >> # defaults to 'localhost'; > >> > >> use '*' > >> for all > >> > >> # (change requires restart) > >> > >> #port = 5432# (change requires restart) > >> > >> I did change the Unix domain socket directories: > >> > >> #unix_socket_directories = '/tmp' # comma-separated list of > >> directories > >> unix_socket_directories = '/tmp,/var/pgsql' # *changed from > >> default > >> # > > > > Your set-up has Apache, PHP and Postgres all running from the same > > machine. > > So as far as running from there goes, it is "localhost". > > The issue is not localhost, it is the case when John is trying to > connect without a host specifier and therefore is trying to reach the > Unix socket. > > > There is no requirement to traverse a network. It is all on the same > > physical machine. > > > > Alter your postgresql.conf file and remove the hash so that:- > > > > listen_addresses = 'localhost' > > > > is explicitly defined. Alter pg_hba.conf so that localhost is declared > > and let's see what happens. > > It will be the same effect, the commented line is just showing that the > default is 'localhost'. Though, John if you do decide to do this > remember to restart the server to have the change take effect. Yes, I learned that lesson years ago! I have been restarting both Apache and Postgresql and closed the browser on every change, and have rebooted a few times too, to ensure that all buffers are not serving stale images. It only takes a minute and keeps a lot of egg off my face! > > > Cheers, > > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote:
> On 04/09/2017 02:35 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 17:02:47 Adrian Klaver wrote:
> >> On 04/09/2017 02:00 PM, John Iliffe wrote:
> >>> On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> >>>> Remember host != local
> >>>>
> >>>> host is for IP connections
> >>>>
> >>>> local is for socket connections
> >>>
> >>> Yes, I had forgotten that for the moment. I have the following line
> >>> in the 'local' section of the pg_hba.conf file:
> >>>
> >>> local all all
> >>> password
> >>>
> >>> and this in the 'host' section
> >>>
> >>> hostall all 127.0.0.1/32
> >>> password
> >>>
> >>> and at the moment I can connect using this:
> >>>
> >>> $db_handle = pg_connect('dbname=yrarc host=localhost port=5432
> >>> user=yrcro password=yrreadonly');
> >>>
> >>> but NOT using this:
> >>>
> >>> $db_handle = pg_connect('dbname=yrarc user=yrcro
> >>> password=yrreadonly');
> >>>
> >>> so I have a problem with the domain sockets.
> >>
> >> I don't think it has been asked and for the sake of completeness,
> >> what do you have listen_addresses set to in postgresql.conf?
> >
> > Still set to the default:
> >
> > #listen_addresses = 'localhost' # what IP address(es) to
> > listen on;
> >
> > # comma-separated list of
>
> Well that would explain why connecting via 192.1.168.x would not work,
> Postgres is only listening on the loopback interface:
>
> https://www.postgresql.org/docs/9.6/static/runtime-config-connection.htm
> l "listen_addresses (string)
>
> Specifies the TCP/IP address(es) on which the server is to listen
> for connections from client applications. The value takes the form of a
> comma-separated list of host names and/or numeric IP addresses. The
> special entry * corresponds to all available IP interfaces. The entry
> 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening
> for all IPv6 addresses. If the list is empty, the server does not listen
> on any IP interface at all, in which case only Unix-domain sockets can
> be used to connect to it. The default value is localhost, which allows
> only local TCP/IP "loopback" connections to be made. While client
> authentication (Chapter 20) allows fine-grained control over who can
> access the server, listen_addresses controls which interfaces accept
> connection attempts, which can help prevent repeated malicious
> connection requests on insecure network interfaces. This parameter can
> only be set at server start.
> "
>
> > addresses;
> >
> > # defaults to 'localhost'; use
> > '*'
> >
> > for all
> >
> > # (change requires restart)
> >
> > #port = 5432# (change requires restart)
> >
> > I did change the Unix domain socket directories:
> >
> > #unix_socket_directories = '/tmp' # comma-separated list of
> > directories
> > unix_socket_directories = '/tmp,/var/pgsql' # *changed from
> > default #
> >
> >> To solve this is going to require starting as close to the Postgres
> >> server as possible and use a consistent connection string between
> >> psql and your PHP code. For the time being I would leave the Apache
> >> server out of the loop as well as your workstation(as much as
> >> possible).
> >>
> >> So:
> >>
> >> 1) Log into the machine with the Postgres server.
> >>
> >> 2) Using psql:
> >>
> >> psql 'dbname=yrarc user=yrcro password=yrreadonly'
> >
> > worked, no problem. Connected to the database and allowed me to
> > select anything as expected.
> >
> >> 3) Using a standalone PHP script:
> >>
> >> $db_handle = pg_connect('dbname=yrarc user=yrcro
> >> password=yrreadonly')
> >
> > Worked perfectly as a standalone PHP programme. Connected and
> > retrieved a record from the database.
>
> So the issue is in PHP via Apache using t
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 18:27:49 rob stone wrote: Hi Rob: Thanks for chiming in. > Hello John, > > Just saw this message. > > > Still set to the default: > > > > #listen_addresses = 'localhost' # what IP address(es) to > > listen on; > > # comma-separated list of > > addresses; > > # defaults to 'localhost'; > > use '*' > > for all > > # (change requires restart) > > #port = 5432# (change requires restart) > > > > I did change the Unix domain socket directories: > > > > #unix_socket_directories = '/tmp' # comma-separated list of > > directories > > unix_socket_directories = '/tmp,/var/pgsql' # *changed from > > default > > # > > Your set-up has Apache, PHP and Postgres all running from the same > machine. > So as far as running from there goes, it is "localhost". > There is no requirement to traverse a network. It is all on the same > physical machine. > > Alter your postgresql.conf file and remove the hash so that:- > > listen_addresses = 'localhost' > > is explicitly defined. Alter pg_hba.conf so that localhost is declared > and let's see what happens. Same as before; the TCP connection works as expected but the Unix domain socket reports --- Sun Apr 09 20:07:02.915744 2017] [php7:warn] [pid 27755:tid 140325136111360] [client 192.168.1.10:36749] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 133 --- > > Cheers, > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 17:39:30 rob stone wrote: > Hello John, > > > > > > > > > > > PHP and Postgresql are both running on same box. It does have > > > > two > > > > > > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme > > > > is > > > > > > available on either. The reference to 192.168.1.10 is the client > > > > machine, in this case my workstation, which is 192.168.1.10. > > > > > > > > > > > > Here is the error message you said came from the box running PHP: > > > >> > -- > > > >> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid > > > >> > 139671464015616] [client 192.168.1.10:59260] PHP Warning: > > > >> > pg_connect(): Unable to connect to PostgreSQL server: could > > > > not > > > > > >> > connect to server: Connection refused\n\tIs the server running > > > > on > > > > > >> > host "192.168.1.6" and accepting\n\tTCP/IP > > > > connections on > > > > > >> > port 5432? in > > > >> > /httpd/iliffe/testfcgi.php on line 132 > > > >> > --- > > > > > > > > This is a tad confusing to me. > > You have Apache, PHP, and Postgres all running on your LAN at > 192.168.1.6. > > > You are on 192.168.1.10. > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to run > that script? testfcgi.php is in the document root of the default named virtual server. > > Cheers, > Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 17:02:47 Adrian Klaver wrote:
> On 04/09/2017 02:00 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> >> Remember host != local
> >>
> >> host is for IP connections
> >>
> >> local is for socket connections
> >
> > Yes, I had forgotten that for the moment. I have the following line
> > in the 'local' section of the pg_hba.conf file:
> >
> > local all all
> > password
> >
> > and this in the 'host' section
> >
> > hostall all 127.0.0.1/32
> > password
> >
> > and at the moment I can connect using this:
> >
> > $db_handle = pg_connect('dbname=yrarc host=localhost port=5432
> > user=yrcro password=yrreadonly');
> >
> > but NOT using this:
> >
> > $db_handle = pg_connect('dbname=yrarc user=yrcro
> > password=yrreadonly');
> >
> > so I have a problem with the domain sockets.
>
> I don't think it has been asked and for the sake of completeness, what
> do you have listen_addresses set to in postgresql.conf?
Still set to the default:
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of
addresses;
# defaults to 'localhost'; use '*'
for all
# (change requires restart)
#port = 5432# (change requires restart)
I did change the Unix domain socket directories:
#unix_socket_directories = '/tmp' # comma-separated list of
directories
unix_socket_directories = '/tmp,/var/pgsql' # *changed from default
#
>
> To solve this is going to require starting as close to the Postgres
> server as possible and use a consistent connection string between psql
> and your PHP code. For the time being I would leave the Apache server
> out of the loop as well as your workstation(as much as possible).
>
> So:
>
> 1) Log into the machine with the Postgres server.
>
> 2) Using psql:
>
> psql 'dbname=yrarc user=yrcro password=yrreadonly'
worked, no problem. Connected to the database and allowed me to select
anything as expected.
>
> 3) Using a standalone PHP script:
>
> $db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly')
>
Worked perfectly as a standalone PHP programme. Connected and retrieved a
record from the database.
> Report back.
>
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there? There isn't any config file for mod_php and php-fpm has
> > one but the location of the domain socket is the default -
> > /tmp/.s...
> >
> > I don't think this is the problem if this list unless someone happens
> > to know the solution. If not, then thank you for all the work, and
> > especially for the promptness of the responses. I'm not at all sure
> > that I could have figured this out by myself.
> >
> > John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> >> On 04/09/2017 11:33 AM, John Iliffe wrote:
> >>> On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> >>>> On 04/08/2017 01:23 PM, John Iliffe wrote:
> >>>>> On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >>>>>> So what if you change the connection to use -h localhost?
> >>>>>
> >>>>> Can you please expand on that request? I'm not sure where you
> >>>>> want me to put that directive. I'm using the mod_php module in
> >>>>> Apache.
> >>>>
> >>>> See the second example here:
> >>>>
> >>>> http://php.net/manual/en/function.pg-connect.php
> >>>>
> >>>> 8<-
> >>>> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> >>>> // connect to a database named "mary" on "localhost" at port "5432"
> >>>> 8<-
> >>>>
> >>>> That will try to use a tcp connection on localhost instead of a
> >>>> unix socket.
> >>>
> >>> Thanks Joe. I Changed the pg_connect line in the script to:
> >>>
> >>> --
> >>>
> >>> $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >>>
> >>> user= password=xx');
> >>> ---
> >>>
> >>> Even though "localhost" is in the /etc/hosts file the lookup failed
> >>> to
> >>
> >>> resolve so I provided the full IP address. The error from Apache is:
> >> You have some very odd issues with your machine...
> >
> > No, Fedora/Red Hat has made a mess! I just installed everything
> > (APache, Postgresql, PHP, OpenSSL from source as I always have over a
> > period of more than 15 years. This is the first time I have run into
> > this sort of problem and it seems to be related to systemctl's unit
> > files.
> >
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system. Very nice on theory but nothing on the details or "What is
> > required?/How do I do it?"
> >
> > In a small company like mine, we are all multi-tasked and having a
> > specialist for everything, like a system programmer skilled in Linux,
> > is just not on. Things have to work right out of the box. Server
> > administration, system operations, etc, is just not my core skill,
> > and I depend heavily on the open source community for help (like you
> > at the moment).
> >
> >>> --
> >>> [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> >>> 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> >>> pg_connect(): Unable to connect to PostgreSQL server: could not
> >>> connect to server: Connection refused\n\tIs the server running on
> >>> host "192.168.1.6" and accepting\n\tTCP/IP connections on
> >>> port 5432? in
> >>> /httpd/iliffe/testfcgi.php on line 132
> >>> ---
> >>>
> >>> PHP does not show anything in its log.
> >>>
> >>>> Another question I don't believe has been asked is, what does your
> >>>> pg_hba.conf look like?
> >>>
> >>> Note here that I have deleted a number of production users and the
> >>> associated databases from the file shown below for security reasons.
> >>> The user marked "XXX" has a real name but isn't the one we are
> >>> using to connect to the database, so the active line should be the
> >>> "local all all password" line. The UID being used to connect IS in
> >>> the password list and PSQL can still connect OK. The yrarc database
> >>> does exist and contains several tables.
> >>>
> >>> --
> >>> # TYPE DATABASEUSERADDRESS
> >>> METHOD
> >
> > i> >
> >
> >>> # "local" is for Unix domain socket connections only
> >>> #local all
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 15:41:12 Joe Conway wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> >> > Thanks Joe. I Changed the pg_connect line in the script to:
> >> > $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >> >
> >> > user= password=xx');
> >
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system.
>
> Maybe so, but as I said earlier if you are in permissive, then blaming
> any of these issues on selinux is wrong -- selinux does no enforcement
> in permissive.
>
> >> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
> >> surprising that cannot connect. You need something like:
> >>
> >> # only allow connections from one host using tcp
> >> hostall all 192.168.1.6/32md5
> >>
> >> - or maybe -
> >>
> >> # only allow connections from same subnet using tcp
> >> hostall all 192.168.1.0/24md5
> >
> > I don't think I should need that since httpd/mod_php is on the same
> > machine so should be 127.0.0.1 should cover it.
>
> No, not if your connection string is
>
> 'dbname=yrarc host=192.168.1.6 port=5432 user= password=xx'
I'm not sure why "localhost" didn't work on the first pass; I just rebooted,
changed the connection string to:
$db_handle = pg_connect('dbname=yrarc host=localhost port=5432
user= password=xxx');
and got a connection. So, it looks like I can connect using TCP on the
loopback. Still doesn't work on the Unix domain socket though.
>
> as you said it was. In this case you must have one of the two pg_hba
> lines above or something more broad. With that connection string you are
> not connecting to localhost, you are connecting to 192.168.1.6 and there
> MUST be a pg_hba line to support it.
>
> > "trust" to avoid any problems with permissions in Postgresql.
>
> Trust has nothing whatsoever to do with the problems you have shown us
> so far.
No, I agree with you, but I just didn't want to open another can of worms.
One of the problems here is too many changes and too many variables. Now
that I can connect I changed it back to "password" and everything still
works.
>
> > PHP and Postgresql are both running on same box. It does have two
> > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme is
> > available on either. The reference to 192.168.1.10 is the client
> > machine, in this case my workstation, which is 192.168.1.10.
>
> Here is the error message you said came from the box running PHP:
> >> > --
> >> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> >> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> >> > pg_connect(): Unable to connect to PostgreSQL server: could not
> >> > connect to server: Connection refused\n\tIs the server running on
> >> > host "192.168.1.6" and accepting\n\tTCP/IP connections on
> >> > port 5432? in
> >> > /httpd/iliffe/testfcgi.php on line 132
> >> > ---
>
No, the workstation doesn't have PHP installed on it.
This message is coming from Apache's error log on 192.168.1.6. Apache logs
the source address of the client (ie my workstation at 192.168.1.10) and
then follows with the PHP error message "PHP Warning: pg_connect(): Unable
to connect to PostgreSQL server: could not connect to server..."
> Now, if your workstation is 192.168.1.10, then I can only conclude that
> you are running PHP on your workstation and *not* on the box with
> Postgres which you say uses 192.168.1.6 and 192.168.1.7.
>
> Joe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 15:05:18 Adrian Klaver wrote:
> On 04/09/2017 11:33 AM, John Iliffe wrote:
> > On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> >> On 04/08/2017 01:23 PM, John Iliffe wrote:
> >>> On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >>>> So what if you change the connection to use -h localhost?
> >>>
> >>> Can you please expand on that request? I'm not sure where you want
> >>> me to put that directive. I'm using the mod_php module in Apache.
> >>
> >> See the second example here:
> >>
> >> http://php.net/manual/en/function.pg-connect.php
> >>
> >> 8<-
> >> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> >> // connect to a database named "mary" on "localhost" at port "5432"
> >> 8<-
> >>
> >> That will try to use a tcp connection on localhost instead of a unix
> >> socket.
> >
> > Thanks Joe. I Changed the pg_connect line in the script to:
> >
> > --
> >
> > $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >
> > user= password=xx');
> > ---
>
> So is this on the machine that has the Postgres server?
Yes, the machine has two addresses, 192.168.1.6 and 192.168.1.7
>
> If not change to that machine and use host=localhost, otherwise just
> change to host=localhost.
>
> > Even though "localhost" is in the /etc/hosts file the lookup failed to
>
> > resolve so I provided the full IP address. The error from Apache is:
> Can you show us the /etc/host file?
--
127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4
::1 localhost localhost.localdomain localhost6
localhost6.localdomain6
192.168.1.1 default
--
I added the blank lines above because the mail client had run them
together. The real file doesn't have any blank lines in it between the host
definitions.
192.168.1.1 is the default Internet gateway. This file was generated by the
Fedora installation programme.
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> On 04/09/2017 11:33 AM, John Iliffe wrote:
> > On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> >> On 04/08/2017 01:23 PM, John Iliffe wrote:
> >> > On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >> >> So what if you change the connection to use -h localhost?
> >> >
> >> > Can you please expand on that request? I'm not sure where you want
> >> > me to put that directive. I'm using the mod_php module in Apache.
> >>
> >> See the second example here:
> >>
> >> http://php.net/manual/en/function.pg-connect.php
> >>
> >> 8<-
> >> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> >> // connect to a database named "mary" on "localhost" at port "5432"
> >> 8<-
> >>
> >> That will try to use a tcp connection on localhost instead of a unix
> >> socket.
> >
> > Thanks Joe. I Changed the pg_connect line in the script to:
> >
> > --
> >
> > $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >
> > user= password=xx');
> > ---
> >
> > Even though "localhost" is in the /etc/hosts file the lookup failed to
>
> > resolve so I provided the full IP address. The error from Apache is:
> You have some very odd issues with your machine...
>
No, Fedora/Red Hat has made a mess! I just installed everything (APache,
Postgresql, PHP, OpenSSL from source as I always have over a period of more
than 15 years. This is the first time I have run into this sort of problem
and it seems to be related to systemctl's unit files.
If I may be permitted a rant at this point, the Fedora documentation is
almost useless for SELinux and much of the underlying operating system.
Very nice on theory but nothing on the details or "What is required?/How do
I do it?"
In a small company like mine, we are all multi-tasked and having a
specialist for everything, like a system programmer skilled in Linux, is
just not on. Things have to work right out of the box. Server
administration, system operations, etc, is just not my core skill, and I
depend heavily on the open source community for help (like you at the
moment).
> > --
> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> > pg_connect(): Unable to connect to PostgreSQL server: could not
> > connect to server: Connection refused\n\tIs the server running on
> > host "192.168.1.6" and accepting\n\tTCP/IP connections on
> > port 5432? in
> > /httpd/iliffe/testfcgi.php on line 132
> > ---
> >
> > PHP does not show anything in its log.
> >
> >> Another question I don't believe has been asked is, what does your
> >> pg_hba.conf look like?
> >
> > Note here that I have deleted a number of production users and the
> > associated databases from the file shown below for security reasons.
> > The user marked "XXX" has a real name but isn't the one we are
> > using to connect to the database, so the active line should be the
> > "local all all password" line. The UID being used to connect IS in
> > the password list and PSQL can still connect OK. The yrarc database
> > does exist and contains several tables.
> >
> > --
> > # TYPE DATABASEUSERADDRESS METHOD
i> >
> > # "local" is for Unix domain socket connections only
> > #local all all md5
> >
> > local yrarc XXX trust
> > local all all
> > password #local all all
> > trust # IPv4 local connections:
> > hostall all 127.0.0.1/32trust
> > -
>
> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
> surprising that cannot connect. You need something like:
>
> # only allow connections from one host using tcp
> hostall all 192.168.1.6/32md5
>
> - or maybe -
>
> # only allow connections from same subnet using tcp
> hostall
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 11:20:29 Joe Conway wrote: > On 04/08/2017 06:31 AM, John Iliffe wrote: > > On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > >> On 04/07/2017 07:45 PM, Joe Conway wrote: > >> > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >> >> On 04/07/2017 05:03 PM, John Iliffe wrote: > >> >>>>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The > >> >>>>> audit log shows no hits on Postgresql. > >> >>> > >> >>> My going in position was/still is, that this is a SELinux > >> >>> security problem > >> >>> but I am finding SELinux to be the most opaque and badly > >> >>> documented software > >> >>> that I have ever had to deal with, which is why it is running in > >> >>> permissive > >> >>> mode at the moment. > >> >> > >> >> Well what I know about SELinux would fit in the navel of a > >> >> flea(tip of the hat to David Niven), so I can not be of much help > >> >> there. The reason I am returned this thread to the list, there > >> >> are folks that do understand it. > >> > > >> > If SELinux is running in permissive I don't see how it could be at > >> > fault for your issue. Did you verify that (getenforce)? > >> > > >> >>> -- > >> >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >> >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >> >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >> >>> connect to server: No such file or directory\n\tIs the server > >> >>> running locally and > >> >>> accepting\n\tconnections on Unix domain socket > >> >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >> >>> line 121 > >> > > >> > This might be a silly question, but is PHP running on the same > >> > server as Postgres? > >> > >> To add to this, previously you mentioned: > >> > >> "Also, using the on board firewall (firewalld) to provide a secondary > >> domain where the actual business processes run. " > >> > >> What exactly does that mean? > > > > There is something rather odd here. > > > > getenforce shows the mode as permissive, which is what I think it is. > > If getenforce shows you are in permissive, then selinux is not your > problem, full stop. > > > BUT, this morning's logwatch report shows: > > *** Denials *** > > > > system_u system_u (tcp_socket): 1 times > > selinux will continue to log denials in permissive -- this is useful to > determine what would have been blocked by selinux had it been in > enforcing, which in turn gives you a chance to fix those issues before > turning on enforcing. > > For more detail on the selinux logs look in /var/log/audit/audit.log > > You definitely have something odd going on though. As you said > elsewhere, using a Unix domain socket connection the firewall should > not get involved either. > > Seems like the issue is related to PHP somehow. For example, see: > http://serverfault.com/questions/641329/cannot-connect-to-postgresql-uni > x-domain-socket In a way, probably yes, but I still can't figure it out. The systemctl unit file DOES have the line PrivateTmp=true and changing it to 'false' didn't accomplish anything. So I did the whole routine, created the /var/pgsql directory, changed the postgresql.conf Unix domain socket line to create two sockets, restarted and checked that both sockets do exist (yes) and then retried the connection. Even stopped and restarted Apache in case something there was required but still no luck. I checked in the PHP directories and there doesn't seem to be any config file that applies to mod_php and in the php-fpm config file there is no reference to postgres. That suggests to me that there is no way to point the connection request to the new socket file location. So, any ideas as to where to go next? FYI, psql still works OK. And, thanks again for your patience! John > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> On 04/08/2017 01:23 PM, John Iliffe wrote:
> > On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >> So what if you change the connection to use -h localhost?
> >
> > Can you please expand on that request? I'm not sure where you want me
> > to put that directive. I'm using the mod_php module in Apache.
>
> See the second example here:
>
> http://php.net/manual/en/function.pg-connect.php
>
> 8<-
> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> // connect to a database named "mary" on "localhost" at port "5432"
> 8<-
>
> That will try to use a tcp connection on localhost instead of a unix
> socket.
>
Thanks Joe. I Changed the pg_connect line in the script to:
--
$db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
user= password=xx');
---
Even though "localhost" is in the /etc/hosts file the lookup failed to
resolve so I provided the full IP address. The error from Apache is:
--
[Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
139671464015616] [client 192.168.1.10:59260] PHP Warning: pg_connect():
Unable to connect to PostgreSQL server: could not connect to server:
Connection refused\n\tIs the server running on host "192.168.1.6"
and accepting\n\tTCP/IP connections on port 5432? in
/httpd/iliffe/testfcgi.php on line 132
---
PHP does not show anything in its log.
> Another question I don't believe has been asked is, what does your
> pg_hba.conf look like?
Note here that I have deleted a number of production users and the
associated databases from the file shown below for security reasons. The
user marked "XXX" has a real name but isn't the one we are using to
connect to the database, so the active line should be the "local all all
password" line. The UID being used to connect IS in the password list and
PSQL can still connect OK. The yrarc database does exist and contains
several tables.
--
# TYPE DATABASEUSERADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all md5
local yrarc XXX trust
local all all password
#local all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
-
>
> Joe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote: > On 04/08/2017 06:26 AM, John Iliffe wrote: > > On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > >> On 04/07/2017 07:45 PM, Joe Conway wrote: > >>> On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >>>> On 04/07/2017 05:03 PM, John Iliffe wrote: > >>>>>>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > >>>>>>> log shows no hits on Postgresql. > >>>>> > >>>>> My going in position was/still is, that this is a SELinux security > >>>>> problem > >>>>> but I am finding SELinux to be the most opaque and badly > >>>>> documented software > >>>>> that I have ever had to deal with, which is why it is running in > >>>>> permissive > >>>>> mode at the moment. > >>>> > >>>> Well what I know about SELinux would fit in the navel of a flea(tip > >>>> of the hat to David Niven), so I can not be of much help there. The > >>>> reason I am returned this thread to the list, there are folks that > >>>> do understand it. > >>> > >>> If SELinux is running in permissive I don't see how it could be at > >>> fault for your issue. Did you verify that (getenforce)? > >>> > >>>>> -- > >>>>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>>>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>>>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>>>> connect to server: No such file or directory\n\tIs the server > >>>>> running locally and > >>>>> accepting\n\tconnections on Unix domain socket > >>>>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>>>> line 121 > >>> > >>> This might be a silly question, but is PHP running on the same > >>> server as Postgres? > >> > >> To add to this, previously you mentioned: > >> > >> "Also, using the on board firewall (firewalld) to provide a secondary > >> domain where the actual business processes run. " > >> > >> What exactly does that mean? > > > > I'm trying/planning to use firewalld to keep certain remote addresses > > from connecting to the mail server. Since I have it anyway, I want > > to strengthen the security by moving non-Internet connections > > internal of that firewall so only Apache is exposed to the Internet > > and the databases, etc, are internal. > > > > This is a Unix domain socket connection so I don't think the firewall > > should get involved. > > So what if you change the connection to use -h localhost? Can you please expand on that request? I'm not sure where you want me to put that directive. I'm using the mod_php module in Apache. > > > Since you raised the question, I added port 5432 to the open list in > > firewalld but it didn't make any difference, still not connecting. > > > >>> HTH, > >>> > >>> Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 09:20:46 Daniel Verite wrote: > John Iliffe wrote: > > The proper socket does exist: > > > > - > > ls -al /tmp | grep PGSQL > > srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > > -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock > > Maybe the httpd service lives in another namespace, > e.g. it's chrooted. What if you try: > > Basically, nothing. If I include it in an Apache script exactly as suggested, then the script puts out a blank screen, no error messages. I used the result in an 'if' statement and it apparently returns false, but that could be an artefact of nothing being returned by file_exists(). It is possible that since the "file" is really a socket, and not a file or directory as required by the documentation, it is not found by however file_exists() looks for files. There is no error log entry from either Apache or PHP. > Best regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > On 04/07/2017 07:45 PM, Joe Conway wrote: > > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >> On 04/07/2017 05:03 PM, John Iliffe wrote: > >>>>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > >>>>> log shows no hits on Postgresql. > >>> > >>> My going in position was/still is, that this is a SELinux security > >>> problem > >>> but I am finding SELinux to be the most opaque and badly documented > >>> software > >>> that I have ever had to deal with, which is why it is running in > >>> permissive > >>> mode at the moment. > >> > >> Well what I know about SELinux would fit in the navel of a flea(tip > >> of the hat to David Niven), so I can not be of much help there. The > >> reason I am returned this thread to the list, there are folks that > >> do understand it. > > > > If SELinux is running in permissive I don't see how it could be at > > fault for your issue. Did you verify that (getenforce)? > > > >>> -- > >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>> connect to server: No such file or directory\n\tIs the server > >>> running locally and > >>> accepting\n\tconnections on Unix domain socket > >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>> line 121 > > > > This might be a silly question, but is PHP running on the same server > > as Postgres? > > To add to this, previously you mentioned: > > "Also, using the on board firewall (firewalld) to provide a secondary > domain where the actual business processes run. " > > What exactly does that mean? > There is something rather odd here. getenforce shows the mode as permissive, which is what I think it is. BUT, this morning's logwatch report shows: *** Denials *** system_u system_u (tcp_socket): 1 times Unfortunately, it doesn't say WHICH stream socket. I'll check that and see if I can find the actual socket that got denied, and if it was actually let through or not. > > HTH, > > > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote: > On 04/07/2017 07:45 PM, Joe Conway wrote: > > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > >> On 04/07/2017 05:03 PM, John Iliffe wrote: > >>>>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > >>>>> log shows no hits on Postgresql. > >>> > >>> My going in position was/still is, that this is a SELinux security > >>> problem > >>> but I am finding SELinux to be the most opaque and badly documented > >>> software > >>> that I have ever had to deal with, which is why it is running in > >>> permissive > >>> mode at the moment. > >> > >> Well what I know about SELinux would fit in the navel of a flea(tip > >> of the hat to David Niven), so I can not be of much help there. The > >> reason I am returned this thread to the list, there are folks that > >> do understand it. > > > > If SELinux is running in permissive I don't see how it could be at > > fault for your issue. Did you verify that (getenforce)? > > > >>> -- > >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>> connect to server: No such file or directory\n\tIs the server > >>> running locally and > >>> accepting\n\tconnections on Unix domain socket > >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>> line 121 > > > > This might be a silly question, but is PHP running on the same server > > as Postgres? > > To add to this, previously you mentioned: > > "Also, using the on board firewall (firewalld) to provide a secondary > domain where the actual business processes run. " > > What exactly does that mean? I'm trying/planning to use firewalld to keep certain remote addresses from connecting to the mail server. Since I have it anyway, I want to strengthen the security by moving non-Internet connections internal of that firewall so only Apache is exposed to the Internet and the databases, etc, are internal. This is a Unix domain socket connection so I don't think the firewall should get involved. Since you raised the question, I added port 5432 to the open list in firewalld but it didn't make any difference, still not connecting. > > > HTH, > > > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Friday 07 April 2017 22:45:16 Joe Conway wrote: > On 04/07/2017 05:35 PM, Adrian Klaver wrote: > > On 04/07/2017 05:03 PM, John Iliffe wrote: > >>>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit > >>>> log shows no hits on Postgresql. > >> > >> My going in position was/still is, that this is a SELinux security > >> problem > >> but I am finding SELinux to be the most opaque and badly documented > >> software > >> that I have ever had to deal with, which is why it is running in > >> permissive > >> mode at the moment. > > > > Well what I know about SELinux would fit in the navel of a flea(tip of > > the hat to David Niven), so I can not be of much help there. The > > reason I am returned this thread to the list, there are folks that do > > understand it. > > If SELinux is running in permissive I don't see how it could be at fault > for your issue. Did you verify that (getenforce)? One would think so. But I'm out of ideas otherwise. I've been chasing this around for several days. > > >> -- > >> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >> pg_connect(): Unable to connect to PostgreSQL server: could not > >> connect to server: No such file or directory\n\tIs the server running > >> locally and > >> accepting\n\tconnections on Unix domain socket > >> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line > >> 121 > > This might be a silly question, but is PHP running on the same server as > Postgres? No question is silly if you don't know the answer :-) Yes, they are both on the same server. > > HTH, > > Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Friday 07 April 2017 20:39:55 Adrian Klaver wrote: > On 04/07/2017 05:10 PM, John Iliffe wrote: > Actually Ccing list this time > > > On Friday 07 April 2017 19:51:57 you wrote: > >> On 04/07/2017 04:57 PM, John Iliffe wrote: > >>> Hi Adrian: > > Well, it ain't that simple! I am trying to take advantage of having a > > new server that doesn't have to be in production until month end to > > update everything to the latest and greatest. Everything runs > > properly on the existing server which is on Postgresql 9.2.1, using > > mod_php to connect. > > > > Changes that I have made are: update Postgresql, PHP, and Apache, > > change to fcgi from mod_php (which should not get involved here, but > > I backed out that change and still doesn't work) and the addition of > > SELinux for security (none on present server). > > Aah, so a lot changed. > > Do you have a way of trying to connect using PHP that does not involve > going through Apache? Yes, running in command line mode under root; the output from one of the cron jobs that hits the database seems to be as expected. It uses a database that hasn't been loaded yet and the error message from the postgresql log says that. (actually it says the role doesn't exist but that is the correct response) The point is, it does connect because it tries to log in. > > > Also, using the on board firewall (firewalld) to provide a secondary > > domain where the actual business processes run. > > > > So, I guess the answer is that the current arrangement has never run > > correctly. > > > >>> Regards, > >>> > >>> John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
On Friday 07 April 2017 20:35:40 Adrian Klaver wrote: > On 04/07/2017 05:03 PM, John Iliffe wrote: > > Please reply to list also Yes, sorry about that. > Ccing list. > > > On Friday 07 April 2017 18:58:15 you wrote: > >> On 04/07/2017 02:38 PM, John Iliffe wrote: > >>> When I attempt to run any web application php cannot open a database > >>> because of failure to connect. (Please disregard the programme > >>> name, it is running in mod_php, not as an fcgi module). The (php) > >>> message is: > >>> > >>> -- > >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning: > >>> pg_connect(): Unable to connect to PostgreSQL server: could not > >>> connect to server: No such file or directory\n\tIs the server > >>> running locally and > >>> accepting\n\tconnections on Unix domain socket > >>> "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on > >>> line 121 > >>> > >>> The proper socket does exist: > >>> > >>> - > >>> ls -al /tmp | grep PGSQL > >>> srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > >>> -rw---. 1 postgres postgres 49 Apr 7 16:53 > >>> .s.PGSQL.5432.lock > >>> > >>> ss -a | grep 5432 > >>> u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480 > >>> > >>> * 0 - > >>> > >>> Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log > >>> shows no hits on Postgresql. > >>> > >>> Postgresql version number is 9.6.2 > >>> > >>> As expected, the postgresql log shows nothing since the last start > >>> up. > >> > >> Meant to add to previous post: > >> > >> What happens if you try to connect to the database using psql? > > > > Works just as I would expect. > > > > In fact, I was able to load the one of the databases from the pg_dump > > backup using pg_restore without any problems either, and I checked the > > results by running some in-stream transactions in psql. Everything > > went fine at that point, until I tried to start Apache and couldn't > > connect. > > To be precise PHP could not connect, correct? Yes. The "unable to connect" message is being issued by PHP. But PHP seems to know what is required (Unix domain socket number and where to find it are both correct as far as I can see. > > > My going in position was/still is, that this is a SELinux security > > problem but I am finding SELinux to be the most opaque and badly > > documented software that I have ever had to deal with, which is why > > it is running in permissive mode at the moment. > > Well what I know about SELinux would fit in the navel of a flea(tip of > the hat to David Niven), so I can not be of much help there. The reason > I am returned this thread to the list, there are folks that do > understand it. > > > Regards, > > > > John > > > >>> Thanks in advance. > >>> > >>> John > >>> = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to connect to Postgresql
Hi Adrian: Not the same problem. Last time I couldn't get postgresql running at all. This time it is running and I can't connect to it. I did check for something else holding the socket, but as far as I can see nothing else has it. Regards, John === On Friday 07 April 2017 18:51:33 Adrian Klaver wrote: > On 04/07/2017 02:38 PM, John Iliffe wrote: > > When I attempt to run any web application php cannot open a database > > because of failure to connect. (Please disregard the programme name, > > it is running in mod_php, not as an fcgi module). The (php) message > > is: > > > > -- > > [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid > > 140599445419776] [client 192.168.1.10:45127] PHP Warning: > > pg_connect(): Unable to connect to PostgreSQL server: could not > > connect to server: No such file or directory\n\tIs the server running > > locally and > > accepting\n\tconnections on Unix domain socket > > "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line > > 121 > > > > The proper socket does exist: > > > > - > > ls -al /tmp | grep PGSQL > > srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > > -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock > > > > ss -a | grep 5432 > > u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480 > > * 0 - > > > > Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log > > shows no hits on Postgresql. > > > > Postgresql version number is 9.6.2 > > > > As expected, the postgresql log shows nothing since the last start up. > > Well the last time this happened the answer was this: > > https://www.postgresql.org/message-id/25543.1489081789%40sss.pgh.pa.us > > > Thanks in advance. > > > > John > > = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to connect to Postgresql
When I attempt to run any web application php cannot open a database because of failure to connect. (Please disregard the programme name, it is running in mod_php, not as an fcgi module). The (php) message is: -- [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid 140599445419776] [client 192.168.1.10:45127] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? in /httpd/iliffe/testfcgi.php on line 121 The proper socket does exist: - ls -al /tmp | grep PGSQL srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock ss -a | grep 5432 u_str LISTEN 0 128/tmp/.s.PGSQL.5432 30480* 0 - Running on Fedora 25 with SELinux in PERMISSIVE mode. The audit log shows no hits on Postgresql. Postgresql version number is 9.6.2 As expected, the postgresql log shows nothing since the last start up. Thanks in advance. John = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large and Growing Group of Files
On Monday 13 March 2017 19:12:10 Tom Lane wrote: > John Iliffe writes: > > Can anybody tell what these files are and what they do, and more > > importantly if they are needed? > > They are database table files, and you will certainly be unhappy if you > just manually rm them. > > You can read some theory here: > > https://www.postgresql.org/docs/9.2/static/storage.html > > and for help in identifying specific files you might like oid2name: > > https://www.postgresql.org/docs/9.2/static/oid2name.html > > although it's certainly possible to get the same results with manual > queries on the system catalogs. > > regards, tom lane Thanks Tom. I was sure they weren't abandoned but the increasing number of them made me wonder if I had a configuration issue. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Large and Growing Group of Files
Can anybody tell what these files are and what they do, and more importantly if they are needed? This database has been in use since about 2012 on PostgreSQL 9.2 and is quite active. (both read and insert/change). There seems to be one group of these files a week, total 316 files as of today. I have truncated the list because all the names are just 5 digit numbers, with every so often a _fsm or _vm extension. They are all in a subdirectory called PG_9.2_201204301/16385. [root@prod03 usr]# ls -al /usr/pgsql_tablespaces/PG_9.2_201204301/16385 | wc -l 316 .. tail end of list. -rw--- 1 postgres postgres 16384 Feb 28 19:07 16479 -rw--- 1 postgres postgres 90112 Mar 13 14:11 16480 -rw--- 1 postgres postgres 114688 Mar 13 18:27 16481 -rw--- 1 postgres postgres 98304 Mar 13 14:11 16482 -rw--- 1 postgres postgres 81920 Mar 13 14:11 16483 -rw--- 1 postgres postgres 90112 Mar 13 14:11 16484 -rw--- 1 postgres postgres 32768 Jun 10 2016 16485 -rw--- 1 postgres postgres 57344 Sep 22 2015 16486 -rw--- 1 postgres postgres 8192 Feb 24 17:00 16527 -rw--- 1 postgres postgres 16384 Feb 26 17:36 16529 -rw--- 1 postgres postgres 24576 Apr 4 2016 16529_fsm -rw--- 1 postgres postgres 8192 Dec 29 2013 16529_vm -rw--- 1 postgres postgres 16384 Feb 24 17:00 16533 -rw--- 1 postgres postgres512 Jan 6 2013 pg_filenode.map -rw--- 1 postgres postgres 111220 Jan 17 19:33 pg_internal.init -rw--- 1 postgres postgres 4 Jan 6 2013 PG_VERSION I have never looked in depth at the database files before; just did it now because I'm working on building a new server! Regards, John == -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Friday 10 March 2017 09:25:25 Melvin Davidson wrote: > On Thu, Mar 9, 2017 at 10:01 PM, John Iliffe wrote: > > On Thursday 09 March 2017 16:37:28 you wrote: > > > On 03/08/2017 10:12 AM, John Iliffe wrote: > > > > I think you may have hit it but I'm even more confused now. > > > > > > > > I looked at the running Postgesql on the current server and there > > > > is a 5th sub-directory called /data. That is on the same level > > > > as the /bin, /share, etc. In this new installation it is not > > > > present and neither is the postgresql.conf file, nor are the hba > > > > files that restrict logins. > > > > > > > > So, my question: is this a change between version 9.2.1 and 9.6.2 > > > > and if so where is the postgresql.conf file (I can't find it on a > > > > scan but it could be renamed I suppose)? > > > > > > > > Or, is there something wrong with the installation? I went by the > > > > successful conclusion message from make install and assumed > > > > everything would be as expected in the directories. > > > > > > > > Any ideas as to what may have happened? > > > > > > Just realized this was an old post that just came through. I also > > > realized that my explanation of where the *.conf files are was > > > specific to a source default install. Package installs may locate > > > them elsewhere. In that case as a Postgres superuser(postgres for > > > example) do: > > > > > > test=# select * from pg_settings where sourcefile is not null; > > > > > > . > > > > > > name| DateStyle > > > setting | ISO, MDY > > > unit| NULL > > > category| Client Connection Defaults / Locale and Formatting > > > short_desc | Sets the display format for date and time values. > > > extra_desc | Also controls interpretation of ambiguous date > > > inputs. context | user > > > vartype | string > > > source | configuration file > > > min_val | NULL > > > max_val | NULL > > > enumvals| NULL > > > boot_val| ISO, MDY > > > reset_val | ISO, MDY > > > sourcefile | /usr/local/pgsql/data/postgresql.conf > > > sourceline | 538 > > > pending_restart | f > > > > > > . > > > > > > And look for the sourcefile location. > > > > > > > John > > > > Thanks Adrian. > > > > I had figured that out eventually, and found the missing files where > > you said > > they were. > > > > Appreciate the info on how to get the configuration values. A lot > > seems to have changed since I started using Postgresql and I guess I > > should really read up on how things work now and not in 2008! > > > > Regards, > > > > John > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > *John,* > > > > > *If you are really serious about reading up, I suggest you pick from the > books on this > url:https://www.packtpub.com/all-books?search=&offset=84&rows=&sort= > <https://www.packtpub.com/all-books?search=&offset=84&rows=&sort=>* > > *Probably "PostgreSQL Administration Essentials" would be good to start > with.* Thank you. I was unaware of this source. I have a similar book but it is about 8 years old so probably out of date. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Thursday 09 March 2017 16:37:28 you wrote: > On 03/08/2017 10:12 AM, John Iliffe wrote: > > I think you may have hit it but I'm even more confused now. > > > > I looked at the running Postgesql on the current server and there is a > > 5th sub-directory called /data. That is on the same level as the > > /bin, /share, etc. In this new installation it is not present and > > neither is the postgresql.conf file, nor are the hba files that > > restrict logins. > > > > So, my question: is this a change between version 9.2.1 and 9.6.2 and > > if so where is the postgresql.conf file (I can't find it on a scan > > but it could be renamed I suppose)? > > > > Or, is there something wrong with the installation? I went by the > > successful conclusion message from make install and assumed everything > > would be as expected in the directories. > > > > Any ideas as to what may have happened? > > Just realized this was an old post that just came through. I also > realized that my explanation of where the *.conf files are was specific > to a source default install. Package installs may locate them elsewhere. > In that case as a Postgres superuser(postgres for example) do: > > test=# select * from pg_settings where sourcefile is not null; > . > > name| DateStyle > setting | ISO, MDY > unit| NULL > category| Client Connection Defaults / Locale and Formatting > short_desc | Sets the display format for date and time values. > extra_desc | Also controls interpretation of ambiguous date inputs. > context | user > vartype | string > source | configuration file > min_val | NULL > max_val | NULL > enumvals| NULL > boot_val| ISO, MDY > reset_val | ISO, MDY > sourcefile | /usr/local/pgsql/data/postgresql.conf > sourceline | 538 > pending_restart | f > . > > And look for the sourcefile location. > > > John Thanks Adrian. I had figured that out eventually, and found the missing files where you said they were. Appreciate the info on how to get the configuration values. A lot seems to have changed since I started using Postgresql and I guess I should really read up on how things work now and not in 2008! Regards, John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
I think you may have hit it but I'm even more confused now. I looked at the running Postgesql on the current server and there is a 5th sub-directory called /data. That is on the same level as the /bin, /share, etc. In this new installation it is not present and neither is the postgresql.conf file, nor are the hba files that restrict logins. So, my question: is this a change between version 9.2.1 and 9.6.2 and if so where is the postgresql.conf file (I can't find it on a scan but it could be renamed I suppose)? Or, is there something wrong with the installation? I went by the successful conclusion message from make install and assumed everything would be as expected in the directories. Any ideas as to what may have happened? John = On Wednesday 08 March 2017 10:11:44 Melvin Davidson wrote: > On Wed, Mar 8, 2017 at 10:00 AM, Tom Lane wrote: > > John Iliffe writes: > > > When the programme exited it left another postmaster.pid file so I > > > > deleted > > > > > that one too. > > > > You haven't shown us the program actually exiting, and basically the > > only way to get the postmaster to exit without removing its pid file > > is to kill -9 it. Now I am suspicious that you in fact haven't > > killed any postmasters, but only removed their pidfiles out from > > under them, which is an incredibly dangerous thing to do. Check "ps > > ax" output to see if any postgres processes are lurking in > > background. > > > > regards, tom lane > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > Hey, looking closer, I see this in your original log > > LOG: database system is ready to accept connections > LOG: autovacuum launcher started > > That means Postgres WAS started, just that the postgres port was unable > to be opened. > So if you do a pg_ctl stop, change the port in postgresql.conf to 5433 > (or 5434) and then attempt > to restart, is your problem resolved? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Thursday 09 March 2017 12:49:49 Tom Lane wrote: > John Iliffe writes: > > On Thursday 09 March 2017 12:08:01 Tom Lane wrote: > >> AFAICS, the explanation must be that getaddrinfo() returned two IPv4 > >> addresses, one of which got bound successfully and the other not. > >> The bleat is about the non-working address, but you still ended up > >> with a working IPv4 socket. > >> > >> This recalls my earlier theory that there's something weird about > >> your networking configuration, but now we have a bit more information > >> about what the weirdness must look like. It might be useful to have > >> a look at "ifconfig" output on your machine. > > > > [root@prod04 John]# ifconfig -a > > enp0s31f6: flags=4163 mtu 1500 > > > > inet 192.168.1.6 netmask 255.255.255.255 broadcast > > 192.168.1.6 inet6 fe80::62d4:f478:8bbb:34a1 prefixlen 64 > > scopeid 0x20 > > > > ... > > lo: flags=73 mtu 65536 > > > > inet 127.0.0.1 netmask 255.0.0.0 > > inet6 ::1 prefixlen 128 scopeid 0x10 > > Ah-hah. You earlier showed your /etc/hosts file: > > > [root(at)prod04 John]# cat /etc/hosts > 127.0.0.1 localhost localhost.localdomain localhost4 > localhost4.localdomain4 > > ::1 localhost localhost.localdomain localhost6 > > localhost6.localdomain6 > 192.168.1.8 prod03.coaxpublications.ca localhost > 192.168.1.1 default > > > So given that, getaddrinfo is going to resolve "localhost" into the > set of addresses "127.0.0.1", "::1", and "192.168.1.8". PG successfully > binds to the first two, which match your "lo" interface, while it fails > to bind to the last one because *that ain't the address on your ethernet > card*. The postmaster will start anyway, as long as it was able to bind > to at least one of the addresses reported for "localhost", so the only > real damage is the confusing log message. > > We could probably have saved a whole lot of time here if the "could not > bind" bleat would print out the specific address that it's failing to > bind to. I have a really vague recollection of having wanted to do that > and not having found any portable library function for converting a > struct addrinfo to a string, but that was years if not decades ago. > We oughta try harder. > > (BTW, it strikes me as pretty insecure to list a non-loopback address > as part of "localhost", but that's a different topic.) > > regards, tom lane Oh... me bad! That was an inexcusable error on my part. I put the prod03 route in the wrong file and I'll have to look up where it should be Not your problem of course. And, of course, it isn't localhost at all. (this machine is prod04) Not sure when I did that. Anyhow, deleted that entry and PostgreSQL started properly with no error messages. Here is the status: start up messages: server stopped [postgres@prod04 pgsql_tablespaces]$ pg_ctl start -D /usr/pgsql_tablespaces server starting [postgres@prod04 pgsql_tablespaces]$ LOG: database system was shut down at 2017-03-09 14:22:17 EST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started Thank you and Adrian for all the trouble and especially for the speed at which you gave me answers. Regards, John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Thursday 09 March 2017 12:08:01 Tom Lane wrote:
> John Iliffe writes:
> > Here is my matching ss output
> >
> > --
> > [root@prod04 John]# ss -l -p | grep post
> > u_str LISTEN 0 128/tmp/.s.PGSQL.5432 69422
> > * 0 users:(("postgres",pid=2760,fd=5))
> > tcpLISTEN 0 128127.0.0.1:postgres *:*
> > users:(("postgres",pid=2760,fd=4))
> > tcpLISTEN 0 128 ::1:postgres :::*
> > users:(("postgres",pid=2760,fd=3))
> > [root@prod04 John]#
> > -
>
> Well, that's absolutely fascinating, because it proves that your
> postmaster (PID 2760) *did* successfully bind to the IPv4 port,
> along with IPv6 and Unix socket too.
>
> So now we're left with the question of why you got this bleat
>
> along the way:
> > [postgres@prod04 pgsql_tablespaces]$ LOG: could not bind IPv4 socket:
> > Cannot assign requested address
>
> AFAICS, the explanation must be that getaddrinfo() returned two IPv4
> addresses, one of which got bound successfully and the other not.
> The bleat is about the non-working address, but you still ended up
> with a working IPv4 socket.
>
> This recalls my earlier theory that there's something weird about
> your networking configuration, but now we have a bit more information
> about what the weirdness must look like. It might be useful to have
> a look at "ifconfig" output on your machine.
>
-
[root@prod04 John]# ifconfig -a
enp0s31f6: flags=4163 mtu 1500
inet 192.168.1.6 netmask 255.255.255.255 broadcast 192.168.1.6
inet6 fe80::62d4:f478:8bbb:34a1 prefixlen 64 scopeid 0x20
ether 38:d5:47:19:0d:cb txqueuelen 1000 (Ethernet)
RX packets 105284 bytes 128902991 (122.9 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 54500 bytes 5397679 (5.1 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
device interrupt 16 memory 0xf700-f702
lo: flags=73 mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10
loop txqueuelen 1 (Local Loopback)
RX packets 7678 bytes 2703160 (2.5 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 7678 bytes 2703160 (2.5 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
-
> Also, are you setting "listen_addresses" to something non-default?
>
No, I made no change to the default listen address in postgresql.conf. In
fact it is commented out.
[root@prod04 John]# cat /usr/pgsql_tablespaces/postgresql.conf | grep
listen
#listen_addresses = 'localhost' # what IP address(es) to listen on;
> > Referring back to an earlier post, I changed the location for the
> > domain socket and lock file from /tmp to /var/run/postgres in the
> > postgresql.conf file and set the permissions so postgres could run.
> > Now psql complains that the domain socket is not present. There
> > doesn't seem to be a config entry or file for psql. What did I miss?
>
> Um ... that we were guessing that you were using a Red-Hat-supplied
> libpq.so that expected the socket to be in /var/run/postgres.
> Evidently that's not the case.
>
No, everything for Postgresql was installed as a separate downloaded
package from the PGSQL web site.
> (I will complain that you're still being absolutely terrible about
> reporting error messages precisely. I do not want to see you say again
> that "program X complains something". Please *copy and paste the exact
> error message* from now on. Even if the details don't seem significant
> to you, they may be to us.)
>
My sincere apologies for this. I appreciate the assistance and the time
that you and Adrian have spent so far.
> regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Thursday 09 March 2017 09:17:51 Adrian Klaver wrote:
> On 03/08/2017 09:28 PM, Tom Lane wrote:
> > John Iliffe writes:
> >> On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
> >>> That isn't proving a lot: as I showed in my example lsof output,
> >>> Fedora's lsof will map "5432" to "postgres" in the context of an IP
> >>> port number. (I'm sure there's a way to turn that off, but -n ain't
> >>> it.)
> >>
> >> Yes, but your lsof output also showed a line for postmaster and mine
> >> doesn't.
> >
> > That's because I started mine by saying "postmaster" not "postgres".
> > It's not real relevant, just ancient habit of mine.
> >
> >> In your case postmaster has an IPv6 TCP socket (but no IPv4 I
> >> notice)
> >
> > Uh, what? I showed an IPv6, an IPv4, and a Unix socket.
> >
> >> The following is from ss, the new version of netstat:
> >>
> >> tcpLISTEN 0 128127.0.0.1:postgres *:*
> >> tcpLISTEN 0 128 ::1:postgres :::*
> >>
> >
> > Well, that's pretty interesting, because it proves that *something*
> > has got IPv4 port 5432 open. If not your manually-started
> > postmaster, then what? You need to inquire into that a bit harder.
> > Running lsof as root and examining all processes might help.
>
> Or using ss, something like:
>
> sudo ss -l -p| grep post
--
> (1) u_str LISTEN 0 128/tmp/.s.PGSQL.5442 15355
> * 0 users:(("postmaster",pid=848,fd=5))
---
> (2) u_str LISTEN 0 128/tmp/.s.PGSQL.5432 15913
* 0 users:(("postmaster",pid=849,fd=5))
--
> (3) tcpLISTEN> 0 128 *:postgresql *:*
>
> users:(("postmaster",pid=849,fd=3))
---
> (4) tcpLISTEN 0 128127.0.0.1:5442 *:*
>
> users:(("postmaster",pid=848,fd=4))
---
> (5) tcpLISTEN 0 128 :::postgresql :::*
>
> users:(("postmaster",pid=849,fd=4))
---
> (6) tcpLISTEN 0 128 ::1:5442 :::*
>
> users:(("postmaster",pid=848,fd=3))
>
> > regards, tom lane
Thanks folks. I'm getting way out of my depth here. My ss output is
below.
***I have reformatted Adrian's output above because word wrap made it
unreadable in this reply. I also numbered the separate lines so I could
reference what I am saying**
Comparing my results with Adrian's example, I notice that we both have the
Unix domain socket 5432 running (1) and we both have an IPv4 and an IPv6
socket open for postgres (3) and (5) BUT he has a couple of TCP sockets
that I don't have.(4) and (6) that are assigned specifically to 5442 in his
case. Shouldn't I have these same lines but assigned to 5432?
I'm assuming that there are two instances of postgresql running on his
machine (?) on sockets 5432 and 5442 (?).
Here is my matching ss output
--
[root@prod04 John]# ss -l -p | grep post
u_str LISTEN 0 128/tmp/.s.PGSQL.5432 69422 *
0 users:(("postgres",pid=2760,fd=5))
tcpLISTEN 0 128127.0.0.1:postgres *:*
users:(("postgres",pid=2760,fd=4))
tcpLISTEN 0 128 ::1:postgres :::*
users:(("postgres",pid=2760,fd=3))
[root@prod04 John]#
-
I also stopped and restarted postgresql to see if SELinux picked up
anything on any tcp activity and it did not, so it doesn't seem to be the
culprit.
I ran netstat and grepped 5432 and no hits so nothing else has this socket.
Just as a test I changed the socket in postgresql.conf to 9876 and tried
again. Same results as above.
[postgres@prod04 pgsql_tablespaces]$ LOG: could not bind IPv4 socket:
Cannot assign requested address
HINT: Is another postmaster already running on port 9876? If not, wait a
few seconds and retry.
Referring back to an earlier post, I changed the location for the domain
socket and lock file from /tmp to /var/run/postgres in the postgresql.conf
file and set the permissions so postgres could run. Now psql complains that
the domain socket is not present. There doesn't seem to be a config entry
or file for psql. What did I miss?
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
> John Iliffe writes:
> > [root@prod04 John]# lsof -n | grep postmaster
> > [root@prod04 John]# lsof -n | grep postgres | grep 5432
> > postgres 2760 postgres5u unix 0x9e5390b5b800
> > 0t0
> >
> > 69422 /tmp/.s.PGSQL.5432 type=STREAM
>
> That isn't proving a lot: as I showed in my example lsof output,
> Fedora's lsof will map "5432" to "postgres" in the context of an IP
> port number. (I'm sure there's a way to turn that off, but -n ain't
> it.)
>
> regards, tom lane
Yes, but your lsof output also showed a line for postmaster and mine
doesn't. In your case postmaster has an IPv6 TCP socket (but no IPv4 I
notice) whereas mine has neither.
The postgres output seems to be equivalent.
As for lsof, I have searched the man page to try and find appropriate
parameters for when all I want to know about is IP sockets but to date no
joy.
The following is from ss, the new version of netstat:
tcpLISTEN 0 128127.0.0.1:postgres *:*
tcpLISTEN 0 128 ::1:postgres :::*
Unfortunately a request to resolve the socket numbers (ss -rl) fails.
What I was able to get is the SELinux context of these sockets but frankly,
it means nothing to me at his point. With SELinux in permissive mode it
shouldn't be relevant, but just in case it helps you, here it is:
---
[root@prod04 John]# ss -aZ | grep post
u_str LISTEN 0 128/tmp/.s.PGSQL.5432 69422 *
0 users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=5))
udpESTAB 0 0 ::1:49481 ::1:49481
users:
(("postgres",pid=2766,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2765,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2764,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2763,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2762,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8))
tcpLISTEN 0 128127.0.0.1:postgres *:*
users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=4))
tcpLISTEN 0 128 ::1:postgres :::*
users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=3))
---
Regards,
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 17:22:21 Adrian Klaver wrote: > On 03/08/2017 01:48 PM, John Iliffe wrote: > > On Wednesday 08 March 2017 15:13:29 Tom Lane wrote: > >> John Iliffe writes: > >>> I tried psql but it won't work, as expected, because socket 5432 is > >>> not available. > >> > >> Actually, that's not all that expected. psql by default would try to > >> connect via a Unix socket, so it wouldn't matter whether or not the > >> postmaster had been able to open an IPv4 port. The most likely > >> reason for failing to connect via Unix socket is looking in the > >> wrong directory for the socket, viz "/tmp" vs "/var/run/postgresql", > >> which is why Adrian is pressing you about other Postgres > >> installations on the machine. If your psql session is using a Red > >> Hat-supplied libpq.so then it will likely look in > >> /var/run/postgresql, whereas this stock-sources postmaster is going > >> to have put it in /tmp by default. (You could adjust the > >> unix_socket_directories parameter to fix that.) It would also help > >> to pay close attention to the error message psql gives when it fails > >> to connect. > >> > >> Even if you are doing "psql -h localhost", I'm pretty sure > >> "localhost" will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on > >> Fedora 25 --- it does on mine. So if the postmaster successfully > >> opened an IPv6 port, which I think it would do by default, then it > >> still wouldn't matter that the IPv4 port wasn't there; the issue > >> should still be masked. > >> > >> FWIW, this is what I see for network sockets when lsof'ing a stock > >> postmaster on current Fedora 25: > >> > >> ... > >> postmaste 20082 tgl3u IPv6 37256 0t0 TCP > >> localhost:postgres (LISTEN) postmaste 20082 tgl4u IPv4 > >> > >> 37257 0t0 TCP localhost:postgres (LISTEN) postmaste 20082 > >> > >> tgl5u unix 0x9eb3435cfc00 0t0 37259 > >> /tmp/.s.PGSQL.5432 type=STREAM ... > >> > >> or with -n it looks like > >> > >> postmaste 20082 tgl3u IPv6 37256 0t0 TCP > >> [::1]:postgres (LISTEN) postmaste 20082 tgl4u IPv4 > >> 37257 0t0 TCP 127.0.0.1:postgres (LISTEN) postmaste 20082 > >> tgl > >> > >>5u unix 0x9eb3435cfc00 0t0 37259 /tmp/.s.PGSQL.5432 > >> > >> type=STREAM > >> > >> What I am suspicious of at this point is that the root of the problem > >> is networking misconfiguration on your machine, such that IPv4 > >> doesn't work at all; given the platform's bias towards IPv6 for > >> loopback, you might not have noticed otherwise. You might check > >> what results you get from "ping ::1" vs "ping 127.0.0.1" vs "ping > >> localhost". > >> > >>regards, tom lane > > > > Hi Tom: > > > > By now you have probably seen that I did get PSQL to run. I had > > assumed that meant that everything was at least running if not as > > expected, at least running. But I did an lsof and I don't get > > anything at all for postmaster. postgres has a lot of open files but > > they are libraries and so forth so not applicable to this situation. > > > > I did the pings you suggest with the following results: > > > > --- > > [root@prod04 John]# ping localhost > > PING localhost(localhost (::1)) 56 data bytes > > 64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms > > 64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms > > 64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms > > 64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms > > 64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms > > ^C > > --- localhost ping statistics --- > > 5 packets transmitted, 5 received, 0% packet loss, time 4084ms > > rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms > > [root@prod04 John]# > > [root@prod04 John]# ping ::1 > > PING ::1(::1) 56 data bytes > > 64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms > > 64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms > > 64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms > > 64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms > > 64 bytes from :
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 17:11:16 Adrian Klaver wrote: > On 03/08/2017 02:01 PM, John Iliffe wrote: > > Please use Reply All so others get your posts. > Ccing list > > > On Wednesday 08 March 2017 16:40:03 you wrote: > >> On 03/08/2017 01:28 PM, John Iliffe wrote: > >>> --- > >>> [root@prod04 John]# su postgres > >>> [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces > >>> could not change directory to "/home/John": Permission denied > >>> server starting > >> > >> This is somewhat suspicious. > > For the list the below is from a previous message which I forgot to Cc. > > >> What if you shutdown the Postgres server and then su - postgres to > >> and > > > >> run?: > > I was starting postgres as user postgres; but it was still in my home > > directory. Everything did start but I changed to /usr/pgsql_dataspaces > > and the message went away. Since there is no requirement to actually > > cd to anything the message is a bit of a red herring. > > > > That said, I'm not sure how it will be handled by the system start up > > task. I'll have to check when I add it. These are unusual manual > > (re)starts. > > > >> pg_ctl start -D /usr/pgsql_tablespaces > >> > >> Along that line what user 'owns' /usr/pgsql_tablespaces? > > > > /usr/pgsql_tablespaces is owned by postgres, group postgres, with > > permissions drwe.. on the directory and rw... on all the > > files. This would seem correct to me. > > > >> Well this indicates the socket is working. > >> > >> What if you do?: > >> > >> psql -U postgres -h ::1 > > > > [root@prod04 John]# psql -U postgres -h ::1 > > psql (9.6.2) > > Type "help" for help. > > So Postgres is binding to IPv6. > > >> That is set in pg_hba.conf. The default is: > >> > >> # "local" is for Unix domain socket connections only > >> local all all trust > > > > Yes, I recall major changes to that file that I did on the current > > server. But that was at least 5 years ago :-( > > > > I have that one fairly tightly locked down since this is a web server. > > > >>>>> lsof says that there is nothing assigned to postmaster at this > >>>>> time. > > Given that you can connect I have to believe lsof would show something, > so what options are you using with lsof and what user are you running it > as? [root@prod04 John]# lsof | grep postmaster [root@prod04 John]# No parameters so I get the whole list. As noted postmaster doesn't find anything whereas I think that I said that the hits on postgres are for the table space, the libraries and such. I did find the following just now for postgres: postgres 1991 postgres5u unix 0x9e53a16edc00 0t0 63085 /tmp/.s.PGSQL.5432 type=STREAM That said, the start up error still shows up, including on the run that I extracted that from. -- [postgres@prod04 John]$ cd /usr/pgsql_tablespaces [postgres@prod04 pgsql_tablespaces]$ pg_ctl start -D /usr/pgsql_tablespaces server starting [postgres@prod04 pgsql_tablespaces]$ LOG: could not bind IPv4 socket: Cannot assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: database system was shut down at 2017-03-08 23:05:14 EST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started [postgres@prod04 pgsql_tablespaces]$ exit exit [root@prod04 John]# lsof -n | grep postmaster [root@prod04 John]# lsof -n | grep postgres | grep 5432 postgres 2760 postgres5u unix 0x9e5390b5b800 0t0 69422 /tmp/.s.PGSQL.5432 type=STREAM [root@prod04 John]# -- This is a domain socket I think, in which case it still isn't getting the IP socket for some reason. Regards, John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 15:13:29 Tom Lane wrote: > John Iliffe writes: > > I tried psql but it won't work, as expected, because socket 5432 is > > not available. > > Actually, that's not all that expected. psql by default would try to > connect via a Unix socket, so it wouldn't matter whether or not the > postmaster had been able to open an IPv4 port. The most likely reason > for failing to connect via Unix socket is looking in the wrong directory > for the socket, viz "/tmp" vs "/var/run/postgresql", which is why Adrian > is pressing you about other Postgres installations on the machine. If > your psql session is using a Red Hat-supplied libpq.so then it will > likely look in /var/run/postgresql, whereas this stock-sources > postmaster is going to have put it in /tmp by default. (You could > adjust the > unix_socket_directories parameter to fix that.) It would also help to > pay close attention to the error message psql gives when it fails to > connect. > > Even if you are doing "psql -h localhost", I'm pretty sure "localhost" > will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on Fedora 25 --- it > does on mine. So if the postmaster successfully opened an IPv6 port, > which I think it would do by default, then it still wouldn't matter that > the IPv4 port wasn't there; the issue should still be masked. > > FWIW, this is what I see for network sockets when lsof'ing a stock > postmaster on current Fedora 25: > > ... > postmaste 20082 tgl3u IPv6 37256 0t0 TCP > localhost:postgres (LISTEN) postmaste 20082 tgl4u IPv4 > 37257 0t0 TCP localhost:postgres (LISTEN) postmaste 20082 > tgl5u unix 0x9eb3435cfc00 0t0 37259 /tmp/.s.PGSQL.5432 > type=STREAM ... > > or with -n it looks like > > postmaste 20082 tgl3u IPv6 37256 0t0 TCP > [::1]:postgres (LISTEN) postmaste 20082 tgl4u IPv4 > 37257 0t0 TCP 127.0.0.1:postgres (LISTEN) postmaste 20082 tgl >5u unix 0x9eb3435cfc00 0t0 37259 /tmp/.s.PGSQL.5432 > type=STREAM > > What I am suspicious of at this point is that the root of the problem is > networking misconfiguration on your machine, such that IPv4 doesn't work > at all; given the platform's bias towards IPv6 for loopback, you might > not have noticed otherwise. You might check what results you get from > "ping ::1" vs "ping 127.0.0.1" vs "ping localhost". > > regards, tom lane Hi Tom: By now you have probably seen that I did get PSQL to run. I had assumed that meant that everything was at least running if not as expected, at least running. But I did an lsof and I don't get anything at all for postmaster. postgres has a lot of open files but they are libraries and so forth so not applicable to this situation. I did the pings you suggest with the following results: --- [root@prod04 John]# ping localhost PING localhost(localhost (::1)) 56 data bytes 64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms 64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms 64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms 64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms 64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms ^C --- localhost ping statistics --- 5 packets transmitted, 5 received, 0% packet loss, time 4084ms rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms [root@prod04 John]# [root@prod04 John]# ping ::1 PING ::1(::1) 56 data bytes 64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms 64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms 64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms 64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms 64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms ^C --- ::1 ping statistics --- 5 packets transmitted, 5 received, 0% packet loss, time 4108ms rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms [root@prod04 John]# [root@prod04 John]# [root@prod04 John]# ping 127.0.0.1 PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data. 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms 64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms 64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms ^C --- 127.0.0.1 ping statistics --- 4 packets transmitted, 4 received, 0% packet loss, time 3066ms rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms [root@prod04 John]# ping 192.168.1.8 <--- current server as a network test PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data. 64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms 64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 11:18:59 Adrian Klaver wrote: > On 03/08/2017 07:37 AM, John Iliffe wrote: > > On Wednesday 08 March 2017 00:01:32 Tom Lane wrote: > >> John Iliffe writes: > >>> Now, running as user postgres I try and start as stated in the > >>> manual postgres -D /usr/pgsql_tablespaces > >>> > >>> The result is: > >>> [postgres@prod04 postgresql-9.6.2]$ postgres -D > >>> /usr/pgsql_tablespaces LOG: could not bind IPv4 socket: Cannot > >>> assign requested address HINT: Is another postmaster already > >>> running on port 5432? If not, wait a few seconds and retry. > >>> LOG: database system was shut down at 2017-03-07 22:22:57 EST > >>> LOG: MultiXact member wraparound protections are now enabled > >>> LOG: database system is ready to accept connections > >>> LOG: autovacuum launcher started > >> > >> To clarify: the postmaster *is* starting here. It failed to bind to > >> the IPv4 port 5432, but it must have succeeded in binding to at > >> least one other port (IPv6 and/or a Unix socket), else it would have > >> stopped and you'd have not seen the last four log lines. > >> > >> It might be helpful to check with lsof to see what the postmaster > >> process has open after you do this. > > > > I noticed that when I deleted the postmaster.pid file as suggested by > > another answer and restarted that process issued a lot more messages > > before crashing :-( Still couldn't connect to port 5432 though. > > Have you tried the firewall setup from here: > > https://fedoraproject.org/wiki/PostgreSQL > Firewall > > PostgreSQL operates on port 5432 (or whatever else you set in your > postgresql.conf). In firewalld you can open it like this: > > $ # make it last after reboot > $ firewall-cmd --permanent --add-port=5432/tcp > $ # change runtime configuration > $ firewall-cmd --add-port=5432/tcp > OK, I tried this, along with some suggestions from other responses. I also rebooted to get a completely clean environment again, and have the following results: 1. the firewall now has port 5432 added permanently. This seems to me to be a security exposure since the socket connection that I need is an INTERNAL (ie on the same machine) connection, not an incoming connection from another machine. Does anyone have any comments on that? 2. The start up messages (still on the screen for convenience) are: --- [root@prod04 John]# su postgres [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces could not change directory to "/home/John": Permission denied server starting [postgres@prod04 John]$ LOG: could not bind IPv4 socket: Cannot assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: database system was shut down at 2017-03-08 10:40:27 EST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started 3. I found the config file (in the tablespace) and changed socket file to /var/run/. That caused a failure of the database since user postgres does not have write authority on /var/run. That isn't the problem at the moment so I'll file it to think about later! There is a socket and a lock file for PGSQL in the /tmp directory. srwxrwxrwx. 1 postgres postgres 0 Mar 8 15:32 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Mar 8 15:32 .s.PGSQL.5432.lock 4. I did a shut down of postmaster to be sure these weren't abandoned files and they disappeared. So I conclude that socket #5432 was, in fact, connected at start up (???) despite what the log says. The pid file also disappeared as expected. 5. Restarted, Same messages as before. The message says fairly specifically that it can't bind an IPv4 socket. Is there a chance that there is an IPv6 socket involved here somewhere that I'm not seeing? 6 Because: psql -U postgres psql (9.6.2) Type "help" for help. postgres=# \l List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 11:37:27 Adrian Klaver wrote: > On 03/08/2017 07:37 AM, John Iliffe wrote: > > On Wednesday 08 March 2017 00:01:32 Tom Lane wrote: > >> John Iliffe writes: > >>> Now, running as user postgres I try and start as stated in the > >>> manual postgres -D /usr/pgsql_tablespaces > >>> > >>> The result is: > >>> [postgres@prod04 postgresql-9.6.2]$ postgres -D > >>> /usr/pgsql_tablespaces LOG: could not bind IPv4 socket: Cannot > >>> assign requested address HINT: Is another postmaster already > >>> running on port 5432? If not, wait a few seconds and retry. > >>> LOG: database system was shut down at 2017-03-07 22:22:57 EST > >>> LOG: MultiXact member wraparound protections are now enabled > >>> LOG: database system is ready to accept connections > >>> LOG: autovacuum launcher started > >> > >> To clarify: the postmaster *is* starting here. It failed to bind to > >> the IPv4 port 5432, but it must have succeeded in binding to at > >> least one other port (IPv6 and/or a Unix socket), else it would have > >> stopped and you'd have not seen the last four log lines. > >> > >> It might be helpful to check with lsof to see what the postmaster > >> process has open after you do this. > > > > I noticed that when I deleted the postmaster.pid file as suggested by > > another answer and restarted that process issued a lot more messages > > before crashing :-( Still couldn't connect to port 5432 though. > > Meant to ask before, can you show the command you are using to connect? Yes: [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces I tried psql but it won't work, as expected, because socket 5432 is not available. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
See embedded. On Wednesday 08 March 2017 00:09:56 Adrian Klaver wrote: > On 03/07/2017 08:17 PM, John Iliffe wrote: > > I was unable to get postgres started so I did a very basic > > compile/install to test it. The configuration line was: > > > > ./configure --prefix=/usr/postgres-9.6.2 > > > > > > Then I ran make which completed as expected: > > > > make[1]: Leaving directory '/tmp/postgresql-9.6.2/config' > > All of PostgreSQL successfully made. Ready to install. > > > > -- > > Then make check > > > > make check > > > > === > > > > All 167 tests passed. > > > > === > > > > make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress' > > > > > > Then make install as user root because of directory permissions. I > > have the expected files in /usr/postgres_9.6.2 > > > > Now, running as user postgres I try and start as stated in the manual > > > > postgres -D /usr/pgsql_tablespaces > > > > initdb has already been run and the directory pgsql_tablespaces has a > > number of files as expected. As yet no database has been defined > > because psql won't start. > > Just to be clear you installed in: > > /usr/postgres-9.6.2 yes, and the expected directories /usr/postgres-9.6.2/bin, /include, /lib, and /share are all there. > > but created the data directory in: > /usr/pgsql_tablespaces Yes, I did that to separate the data from the software in case I decide to update postgresql in the future. That way I can always revert if necessary. > Also above you say: > > "I was unable to get postgres started so I did a very basic > compile/install to test it. " > Yes, with no changes except the --prefix in the config file I was assured that I wasn't causing this problem with a badly chosen parameter. > To me that implies there is another instance of Postgres on the system, > is that the case? > No other instances of Postgres on the system. Since it wasn't working I deleted the original installation by deleting the install directory. > If not could you explain what you meant? > > > The result is: > > [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces > > LOG: could not bind IPv4 socket: Cannot assign requested address > > HINT: Is another postmaster already running on port 5432? If not, > > wait a few seconds and retry. > > LOG: database system was shut down at 2017-03-07 22:22:57 EST > > LOG: MultiXact member wraparound protections are now enabled > > LOG: database system is ready to accept connections > > LOG: autovacuum launcher started > > > > Same results if I use pg_ctl to start the process. > > > > I checked with lsof and there is no process bound to socket 5432. > > There is no entry in /var/run for a socket related to postgresql. > > > > I thought it might be a security issue so I put SELinux in permissive > > mode but the result is the same. The SELinux journal does not show > > any warnings on this process. > > > > [root@prod04 postgresql-9.6.2]# sestatus > > SELinux status: enabled > > SELinuxfs mount:/sys/fs/selinux > > SELinux root directory: /etc/selinux > > Loaded policy name: targeted > > Current mode: permissive <--permissive mode** > > Mode from config file: permissive > > Policy MLS status: enabled > > Policy deny_unknown status: allowed > > Max kernel policy version: 30 > > - > > > > One thing that I haven't been able to find any the log files. Where > > are they normally stored? > > > > So, any ideas as to where to go next to debug this would be > > appreciated! This is a brand new server that I am trying to configure > > so I have a fair amount of security clearance to chase things. > > > > Thanks in advance. > > > > John > > = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to start postgresql
On Wednesday 08 March 2017 00:01:32 Tom Lane wrote: > John Iliffe writes: > > Now, running as user postgres I try and start as stated in the manual > > postgres -D /usr/pgsql_tablespaces > > > > The result is: > > [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces > > LOG: could not bind IPv4 socket: Cannot assign requested address > > HINT: Is another postmaster already running on port 5432? If not, > > wait a few seconds and retry. > > LOG: database system was shut down at 2017-03-07 22:22:57 EST > > LOG: MultiXact member wraparound protections are now enabled > > LOG: database system is ready to accept connections > > LOG: autovacuum launcher started > > To clarify: the postmaster *is* starting here. It failed to bind to the > IPv4 port 5432, but it must have succeeded in binding to at least one > other port (IPv6 and/or a Unix socket), else it would have stopped and > you'd have not seen the last four log lines. > > It might be helpful to check with lsof to see what the postmaster > process has open after you do this. > I noticed that when I deleted the postmaster.pid file as suggested by another answer and restarted that process issued a lot more messages before crashing :-( Still couldn't connect to port 5432 though. --- [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces could not change directory to "/home/John": Permission denied server starting [postgres@prod04 John]$ LOG: could not bind IPv4 socket: Cannot assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: database system was interrupted; last known up at 2017-03-08 09:42:16 EST LOG: database system was not properly shut down; automatic recovery in progress LOG: invalid record length at 0/1561138: wanted 24, got 0 LOG: redo is not required LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started - so I corrected the initial error by changing to the bin directory and starting again, after removing the postmaster.pid file. Same result. lsof says that there is nothing assigned to postmaster at this time. I did manage to get a clean stop this time; no remaining pid file. > > I checked with lsof and there is no process bound to socket 5432. > > There is no entry in /var/run for a socket related to postgresql. > > With the default configure options you used, the postmaster would have > put its Unix socket file into /tmp, not /var/run. I wonder whether > your problem is that you're trying to connect to it with > distro-supplied psql+libpq that expects to find the Unix socket in > /var/run. > Yes. socket file and also lock file were there. I'll fix that in config, BUT in the original case they weren't there. srwxrwxrwx. 1 postgres postgres0 Mar 8 10:10 .s.PGSQL.5432 -rw---. 1 postgres postgres 49 Mar 8 10:10 .s.PGSQL.5432.lock Still, the first lines of the log are the same; can't connect to socket 5432. The following processes show up in ps root 1149 1136 0 10:18 pts/100:00:00 su postgres postgres 1150 1149 0 10:18 pts/100:00:00 bash postgres 1230 1 0 10:26 pts/100:00:00 /usr/postgres-9.6.2/bin/postgres -D /usr/pgsql_tablespaces postgres 1232 1230 0 10:26 ?00:00:00 postgres: checkpointer process postgres 1233 1230 0 10:26 ?00:00:00 postgres: writer process postgres 1234 1230 0 10:26 ?00:00:00 postgres: wal writer process postgres 1235 1230 0 10:26 ?00:00:00 postgres: autovacuum launcher process postgres 1236 1230 0 10:26 ?00:00:00 postgres: stats collector process > > One thing that I haven't been able to find any the log files. Where > > are they normally stored? > > They seem to be showing up on postmaster's stderr (ie, your terminal) > which again is the vanilla-configuration default if you didn't do > anything to edit the postgresql.conf settings. > > I suspect that you're used to the behavior of a vendor-configured > postgres package and have not taken the steps needed to make a build > from source behave the same way. Recommend looking into what patches > the vendor package applies and what configure options are used. > No, actually I have been using postgresql since about 2007, always from manual installs. Just never needed to ask a question before! I run a small publishing business specializing an Amateur Radio training and we rely on these databases to run just about everything from sales to user support to accounting. At the
Re: [GENERAL] Unable to start postgresql
Yes, that file was present, so I deleted it. Attempted to restart and got the same result: LOG: could not bind IPv4 socket: Cannot assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: database system was interrupted; last known up at 2017-03-07 22:48:24 EST LOG: database system was not properly shut down; automatic recovery in progress LOG: invalid record length at 0/15610C8: wanted 24, got 0 LOG: redo is not required LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started When the programme exited it left another postmaster.pid file so I deleted that one too. John === On Tuesday 07 March 2017 23:26:40 Melvin Davidson wrote: > On Tue, Mar 7, 2017 at 11:17 PM, John Iliffe wrote: > > I was unable to get postgres started so I did a very basic > > compile/install to test it. The configuration line was: > > > > ./configure --prefix=/usr/postgres-9.6.2 > > > > > > Then I ran make which completed as expected: > > > > make[1]: Leaving directory '/tmp/postgresql-9.6.2/config' > > All of PostgreSQL successfully made. Ready to install. > > > > -- > > Then make check > > > > make check > > > > === > > > > All 167 tests passed. > > > > === > > > > make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress' > > > > > > Then make install as user root because of directory permissions. I > > have the expected files in /usr/postgres_9.6.2 > > > > Now, running as user postgres I try and start as stated in the manual > > > > postgres -D /usr/pgsql_tablespaces > > > > initdb has already been run and the directory pgsql_tablespaces has a > > number of files as expected. As yet no database has been defined > > because psql won't start. > > > > The result is: > > [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces > > LOG: could not bind IPv4 socket: Cannot assign requested address > > HINT: Is another postmaster already running on port 5432? If not, > > wait a few seconds and retry. > > LOG: database system was shut down at 2017-03-07 22:22:57 EST > > LOG: MultiXact member wraparound protections are now enabled > > LOG: database system is ready to accept connections > > LOG: autovacuum launcher started > > > > Same results if I use pg_ctl to start the process. > > > > I checked with lsof and there is no process bound to socket 5432. > > There is no entry in /var/run for a socket related to postgresql. > > > > I thought it might be a security issue so I put SELinux in permissive > > mode but the result is the same. The SELinux journal does not show > > any warnings on this process. > > > > [root@prod04 postgresql-9.6.2]# sestatus > > SELinux status: enabled > > SELinuxfs mount:/sys/fs/selinux > > SELinux root directory: /etc/selinux > > Loaded policy name: targeted > > Current mode: permissive <--permissive mode** > > Mode from config file: permissive > > Policy MLS status: enabled > > Policy deny_unknown status: allowed > > Max kernel policy version: 30 > > - > > > > One thing that I haven't been able to find any the log files. Where > > are they > > normally stored? > > > > So, any ideas as to where to go next to debug this would be > > appreciated! This is a brand new server that I am trying to configure > > so I have a fair amount of security clearance to chase things. > > > > Thanks in advance. > > > > John > > = > > > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > This is very suspicicious. > > >LOG: could not bind IPv4 socket: Cannot assign requested address > >HINT: Is another postmaster already running on port 5432? If not, wait > >a > > f>ew seconds and retry. > > So check to see if the file "postmaster.pid" exists. > If it does, and postgres is NOT running, just delet or rename it and try > to start. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to start postgresql
I was unable to get postgres started so I did a very basic compile/install to test it. The configuration line was: ./configure --prefix=/usr/postgres-9.6.2 Then I ran make which completed as expected: make[1]: Leaving directory '/tmp/postgresql-9.6.2/config' All of PostgreSQL successfully made. Ready to install. -- Then make check make check === All 167 tests passed. === make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress' Then make install as user root because of directory permissions. I have the expected files in /usr/postgres_9.6.2 Now, running as user postgres I try and start as stated in the manual postgres -D /usr/pgsql_tablespaces initdb has already been run and the directory pgsql_tablespaces has a number of files as expected. As yet no database has been defined because psql won't start. The result is: [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces LOG: could not bind IPv4 socket: Cannot assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: database system was shut down at 2017-03-07 22:22:57 EST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started Same results if I use pg_ctl to start the process. I checked with lsof and there is no process bound to socket 5432. There is no entry in /var/run for a socket related to postgresql. I thought it might be a security issue so I put SELinux in permissive mode but the result is the same. The SELinux journal does not show any warnings on this process. [root@prod04 postgresql-9.6.2]# sestatus SELinux status: enabled SELinuxfs mount:/sys/fs/selinux SELinux root directory: /etc/selinux Loaded policy name: targeted Current mode: permissive <--permissive mode** Mode from config file: permissive Policy MLS status: enabled Policy deny_unknown status: allowed Max kernel policy version: 30 - One thing that I haven't been able to find any the log files. Where are they normally stored? So, any ideas as to where to go next to debug this would be appreciated! This is a brand new server that I am trying to configure so I have a fair amount of security clearance to chase things. Thanks in advance. John = -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGSQL 9.6.2 unable to find readline
Perfect! Thank you. Same issue then occurred with zlib and I installed the dev pieces and it worked too. Might be a good idea if the documentation listed these somewhere as prereqs. In the past all the servers I have worked on had these installed already but this one is a bit basic. Regards, and again, thanks. John === On Tuesday 07 March 2017 16:12:22 Devrim Gündüz wrote: > Hi, > > On Tue, 2017-03-07 at 15:38 -0500, John Iliffe wrote: > > Trying to compile pgsql 9.6.2 on Fedora 25 > > > > I get the following message: > > > > configure:9345: error: readline library not found > > Please install readline-devel. > > (BTW, https://yum.PostgreSQL.org has 9.6.2 and Fedora 25 RPMs already) > > Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGSQL 9.6.2 unable to find readline
Trying to compile pgsql 9.6.2 on Fedora 25 I get the following message: configure:9345: error: readline library not found If you have readline already installed, see config.log for details on the Use --without-readline to disable readline support. pgac_cv_check_readline=no libreadline does exist: [John@prod04 postgresql-9.6.2]$ rpm -qv readline readline-6.3-8.fc24.x86_64 Other relevant info: gcc 6.3.1 command line: ../configure --prefix=/usr/postgres-9.6.2 --with-openssl --with- libraries=/usr/lib64 libreadline is in /usr/lib64 ls -l /usr/lib64/libread* lrwxrwxrwx. 1 root root 18 Feb 4 2016 /usr/lib64/libreadline.so.6 -> libreadline.so.6.3 -rwxr-xr-x. 1 root root 296072 Feb 4 2016 /usr/lib64/libreadline.so.6.3 This is similar to other posts regarding pgsql 9.3 in the archive but has anyone found the cause yet? Thanks. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Database Backups
Thanks to those who replied to my post. I tried that and works a treat! Regards. John == On Sat, 2010-16-10 at 17:18 +0100, Raymond O'Donnell wrote: > On 15/10/2010 22:12, John Iliffe wrote: > > Does anyone have a way to run pg_dump from cron in a way that doesn't > > require someone to enter the password on every run? I get the following > > error even when the backup user has read permissions on all application > > databases. > > You need to use a .pgpasss file: > > http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html > > You can instead specify the password in an environment variable, but > that potentially makes it visible to all users on the system, so the > .pgpass is better. > > HTH, > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Automated Database Backups
Does anyone have a way to run pg_dump from cron in a way that doesn't require someone to enter the password on every run? I get the following error even when the backup user has read permissions on all application databases. The database that fails is part of the PostgreSQL kernel, not my application data so I'm leery of tampering with permissions here. The command line I am using is: /usr/postgres-8.4.2/bin/pg_dump -f /notraid/dbbkup.`date +%Y%m%d` -F custom -v -U db Thanks in advance. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
