Re: Errors when restoring backup created by pg_dumpall

[Tom Lane]

PopeRigby  writes:
> I've applied the following patch to postgres:
> https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

Cool.  You did actually install the new scripts into your target
installation, right?

> I'm still getting this error:
> psql:all.sql:4102: ERROR:  type "earth" does not exist
> LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

Hmmm ... a pg_dumpall output script shouldn't really contain that
function body directly; it should just say "CREATE EXTENSION
earthdistance".  Is it possible that this database is so old that
it contains a pre-extension (pre-9.1) version of earthdistance?

regards, tom lane

Re: Errors when restoring backup created by pg_dumpall

[PopeRigby]

On 11/30/24 19:58, Tom Lane wrote:

"David G. Johnston"  writes:

Ok, so the error is not emanating from your code but rather the body of the
ll_to_earth function defined in the earthdistance extension.

Yeah.  That is

CREATE FUNCTION ll_to_earth(float8, float8)
RETURNS earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT 
cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';

which is problematic: the function calls are not schema-qualified and
there's not a really easy way to fix that.  There's work afoot to fix
that [1], but it's not committed yet let alone in any shipping
version.  Nonetheless, your best bet for fixing this might be
to install the earthdistance 1.2 files from the latest patchset in
that thread.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/3395418.1618352...@sss.pgh.pa.us


I've applied the following patch to postgres:

https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

My problem is related to
https://github.com/docker-library/postgres/pull/440/files

But I am yet not sure how to enable listening at localhost again

Re: Errors when restoring backup created by pg_dumpall

[Adrian Klaver]

On 12/1/24 12:05, PopeRigby wrote:

On 11/30/24 19:58, Tom Lane wrote:

"David G. Johnston"  writes:
Ok, so the error is not emanating from your code but rather the body 
of the

ll_to_earth function defined in the earthdistance extension.

Yeah.  That is

CREATE FUNCTION ll_to_earth(float8, float8)
RETURNS earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT 
cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';


which is problematic: the function calls are not schema-qualified and
there's not a really easy way to fix that.  There's work afoot to fix
that [1], but it's not committed yet let alone in any shipping
version.  Nonetheless, your best bet for fixing this might be
to install the earthdistance 1.2 files from the latest patchset in
that thread.

    regards, tom lane

[1] 
https://www.postgresql.org/message-id/flat/3395418.1618352...@sss.pgh.pa.us


I've applied the following patch to postgres:

https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth



The issue is still this:

SELECT pg_catalog.set_config('search_path', '', false);

in the pg_dumpall output.

As was suggested before change the above to:

SELECT pg_catalog.set_config('search_path', 'public', false);

in the pg_dumpall output file.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Tom Lane]

Alexander Farber  writes:
> it is empty in my docker container (but "localhost" on my prod server),
> so I probably need to investigate that...

I'm betting the postgresql.conf entry is being overridden with a
postmaster command-line switch (not an initdb switch, which merely
sets up a postgresql.conf entry).

regards, tom lane

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Adrian Klaver]

On 12/1/24 11:34, Alexander Farber wrote:

Good evening,

I am trying to create a Dockerfile for development purposes, which
would run Jetty and PostgreSQL. The PostgreSQL related part is below:




When I connect to the docker container via terminal,
I am able to connect with "psql words_de" command.


The terminal is in the container host?

If so does the host have an instance of Postgres running on it?



But the "psql -h localhost words_de" command fails:




Best regards
Alex



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Errors when restoring backup created by pg_dumpall

[Tom Lane]

Adrian Klaver  writes:
> On 12/1/24 12:05, PopeRigby wrote:
>> I'm still getting this error:
>> 
>> psql:all.sql:4102: ERROR:  type "earth" does not exist
>> LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

> The issue is still this:
> SELECT pg_catalog.set_config('search_path', '', false);
> in the pg_dumpall output.

We've done that for some time, though.

> As was suggested before change the above to:
> SELECT pg_catalog.set_config('search_path', 'public', false);
> in the pg_dumpall output file.

I'm betting that won't help.  The new-in-17 behavior is that
maintenance commands such as CREATE INDEX internally force
a "safe" search_path, regardless of the prevailing setting.

It would be useful to know what is the command at line 4102
of all.sql.

regards, tom lane

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Peter Kleiner]

Seems too easy but have you tried

psql -p 6432 -h localhost words_de

?


On Sun, Dec 1, 2024 at 3:59 PM Alexander Farber 
wrote:

> My problem is related to
> https://github.com/docker-library/postgres/pull/440/files
>
> But I am yet not sure how to enable listening at localhost again
>

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

Yes thank you, I have tried specifying the port at the CLI too... that did
not help.

But restarting PostgreSQL in my
custom /docker-entrypoint-initdb.d/run-after-initdb.sh
has helped, even though I am not sure if it is the best way:

#!/bin/sh

LANGUAGES="de en fr nl pl ru"

createuser --username=postgres words

psql --username=postgres -c "GRANT USAGE ON SCHEMA public TO words;"
psql --username=postgres -c "ALTER USER words PASSWORD 'mypassword';"

for L in $LANGUAGES; do
createdb --username=postgres --owner=words words_$L
cd /dict/$L && psql words_$L < words_$L.sql
done

# Restart PostgreSQL to make it listen at localhost too
pg_ctl --options "-c listen_addresses='localhost'" --wait restart

Help with syntax error

[Arbol One]

I have these tables:

*CREATE TABLE IF NOT EXISTS registrar (
index SERIAL,
registrar_id TEXT,
day TEXT NOT NULL,
month TEXT NOT NULL,
year TEXT NOT NULL,
hour TEXT NOT NULL,
minute TEXT NOT NULL,
second TEXT NOT NULL,
millisecond TEXT NOT NULL,
am_pm TEXT NOT NULL,
admin BOOLEAN NOT NULL DEFAULT false,
CONSTRAINT key PRIMARY KEY (registrar_id) );

CREATE TABLE IF NOT EXISTS user_name (
user_name_id TEXT PRIMARY KEY REFERENCES registrar(registrar_id),
first_name TEXT NOT NULL,
middle_name TEXT NOT NULL,
last_name TEXT NOT NULL);*

When I enter the following pg commands :

*INSERT INTO registrar VALUES (DEFAULT, '18551420601012L23', '1', '12', 
'2024', '18', '55', '14', '207', 'PM');
INSERT INTO user_name '18551420601012L23', 'John', 'Fitzgerald', 
'Kennedy'); <= **/UserNameTable.write()/*


I get this message :

*/Exception in thread "main" java.sql.SQLException: In 
company.contact.Company$UserNameTable.write()

ERROR: syntax error at or near "'18551420601012L23'"
  Position: 23/
*

The error is in the user_name_id, which is a primary that references the 
table "*registrar*" 's "*registrar_id" *primary key.
According to the documentation 
, the 
insertion should be OK, no?


What am I doing wrong?


--
*/ArbolOne ™/*
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing 
free services to charitable organizations.
ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
progress [ í ]

Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

Good evening,

I am trying to create a Dockerfile for development purposes, which
would run Jetty and PostgreSQL. The PostgreSQL related part is below:

FROM postgres:17-alpine3.20
RUN apk update && apk upgrade && apk add --no-cache curl wget openjdk21 tini

# Tell docker-entrypoint.sh to create superuser "postgres"
# with password "mypassword" and database "postgres"
ENV POSTGRES_PASSWORD mypassword

# Tell docker-entrypoint.sh to change these params in postgresql.conf
ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB"

# The PostgreSQL port is changed from 5432 to 6432 to emulate pg_bouncer
ENV PGPORT 6432
ENV PGUSER words
ENV PGPASSWORD mypassword

WORKDIR /docker-entrypoint-initdb.d
COPY ["run-after-initdb.sh", "."]
RUN chmod +x run-after-initdb.sh && cat run-after-initdb.sh

USER postgres

EXPOSE 6432 9090

ENTRYPOINT ["/sbin/tini", "--"]

CMD ["sh", "-c", "docker-entrypoint.sh postgres & cd $JETTY_BASE && sleep
10 && java -Djdbc.drivers=org.postgresql.Driver -jar $JETTY_HOME/start.jar"]

And below is my run-after-initdb.sh script:

#!/bin/sh

createuser --username=postgres words

psql --username=postgres -c "GRANT USAGE ON SCHEMA public TO words;"
psql --username=postgres -c "ALTER USER words PASSWORD 'mypassword';"

for L in de en ru fr nl pl; do
createdb --username=postgres --owner=words words_$L
cd /dict/$L && psql words_$L < words_$L.sql
done

Then I build and run the docker image and that works without errors:

# docker build -t my_docker_image --progress=plain .
# docker run --name my_container -p 8080:8080 -p 6432:6432 my_docker_image

When I connect to the docker container via terminal,
I am able to connect with "psql words_de" command.

But the "psql -h localhost words_de" command fails:

psql: error: connection to server at "localhost" (127.0.0.1), port 6432
failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 6432 failed: Address not
available
Is the server running on that host and accepting TCP/IP connections?

I need the connection via 127.0.0.1 though,
because of my Java servlet hosted by the Jetty.

I have looked at the config files and env vars:

# ls -al /var/run/postgresql/
srwxrwxrwx1 postgres postgres 0 Dec  1 19:06 .s.PGSQL.6432
-rw---1 postgres postgres64 Dec  1 19:06 .s.PGSQL.6432.lock

# echo $PGDATA
/var/lib/postgresql/data

# grep -v '^#' $PGDATA/pg_hba.conf
local   all all trust
hostall all 127.0.0.1/32trust
hostall all ::1/128 trust
local   replication all trust
hostreplication all 127.0.0.1/32trust
hostreplication all ::1/128 trust

# grep -v '^.*#' $PGDATA/postgresql.conf
listen_addresses = '*'
max_wal_size = 2GB
min_wal_size = 80MB
log_timezone = UTC
datestyle = 'iso, mdy'
timezone = UTC
default_text_search_config = 'pg_catalog.english'

Is anybody able spotting, what am I doing wrong here? Thank you

TLDR "psql words_de" works, but "psql -h localhost words_de" does not

Best regards
Alex

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Tom Lane]

Alexander Farber  writes:
> TLDR "psql words_de" works, but "psql -h localhost words_de" does not

I'd try connecting the first way and seeing what "show
listen_addresses" gives.  Per your report it should be "*",
but maybe something is overriding that.

If it is "*", then it seems like something is interfering with
expanding that.  Maybe try explicitly setting it to "localhost"?

regards, tom lane

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

Thank you, Tom -

On Sun, Dec 1, 2024 at 8:58 PM Tom Lane  wrote:

> Alexander Farber  writes:
> > TLDR "psql words_de" works, but "psql -h localhost words_de" does not
>
> I'd try connecting the first way and seeing what "show
> listen_addresses" gives.  Per your report it should be "*",
> but maybe something is overriding that.
>
> If it is "*", then it seems like something is interfering with
> expanding that.  Maybe try explicitly setting it to "localhost"?
>

it is empty in my docker container (but "localhost" on my prod server),
so I probably need to investigate that...

$ psql words_de
psql (17.2)
Type "help" for help.

words_de=> show listen_addresses;
 listen_addresses
--

(1 row)

words_de=> set listen_addresses='localhost';
ERROR:  parameter "listen_addresses" cannot be changed without restarting
the server

# grep listen_addresses $PGDATA/postgresql.conf
listen_addresses = '*'

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

[Alexander Farber]

I have tried changing the line in the Dockerfile, but it has not helped yet:

ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB --set listen_addresses=localhost"

ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB --set listen_addresses='localhost'"

Maybe the quotes get lost somehow when using the "--set" option?

$ grep listen $PGDATA/postgresql.conf
listen_addresses = localhost

Re: Errors when restoring backup created by pg_dumpall

[Adrian Klaver]

On 12/1/24 13:14, Tom Lane wrote:

Adrian Klaver  writes:

On 12/1/24 12:05, PopeRigby wrote:

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth



The issue is still this:
SELECT pg_catalog.set_config('search_path', '', false);
in the pg_dumpall output.


We've done that for some time, though.


As was suggested before change the above to:
SELECT pg_catalog.set_config('search_path', 'public', false);
in the pg_dumpall output file.


I'm betting that won't help.  The new-in-17 behavior is that
maintenance commands such as CREATE INDEX internally force
a "safe" search_path, regardless of the prevailing setting.

It would be useful to know what is the command at line 4102
of all.sql.


It is here:

https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

CREATE TABLE public.geodata_places (
id integer NOT NULL,
name character varying(200) NOT NULL,
longitude double precision NOT NULL,
latitude double precision NOT NULL,
"countryCode" character(2) NOT NULL,
"admin1Code" character varying(20),
"admin2Code" character varying(80),
"modificationDate" date NOT NULL,
"earthCoord" public.earth GENERATED ALWAYS AS 
(public.ll_to_earth(latitude, longitude)) STORED,

"admin1Name" character varying,
"admin2Name" character varying,
"alternateNames" character varying
);




regards, tom lane


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Errors when restoring backup created by pg_dumpall

[Tom Lane]

Adrian Klaver  writes:
> On 12/1/24 13:14, Tom Lane wrote:
>> It would be useful to know what is the command at line 4102
>> of all.sql.

> It is here:

> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

> CREATE TABLE public.geodata_places (
>  id integer NOT NULL,
>  name character varying(200) NOT NULL,
>  longitude double precision NOT NULL,
>  latitude double precision NOT NULL,
>  "countryCode" character(2) NOT NULL,
>  "admin1Code" character varying(20),
>  "admin2Code" character varying(80),
>  "modificationDate" date NOT NULL,
>  "earthCoord" public.earth GENERATED ALWAYS AS 
> (public.ll_to_earth(latitude, longitude)) STORED,
>  "admin1Name" character varying,
>  "admin2Name" character varying,
>  "alternateNames" character varying
> );

Ah!  Then the failure occurs because we do a planning pass on the
GENERATED expression (I don't remember exactly why that's needed
during CREATE TABLE).  So maybe messing with the dump script's
search_path setting *would* be enough to get you past that.

Having said that, the CREATE should have been seeing the new-style
definition of ll_to_earth() if the 1.2 version of earthdistance
was correctly installed.

regards, tom lane

Re: Help with syntax error

[Ron Johnson]

On Sun, Dec 1, 2024 at 7:14 PM Arbol One  wrote:

> I have these tables:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *CREATE TABLE IF NOT EXISTS registrar ( index SERIAL, registrar_id TEXT,
> day TEXT NOT NULL, month TEXT NOT NULL, year TEXT NOT NULL, hour TEXT NOT
> NULL, minute TEXT NOT NULL, second TEXT NOT NULL, millisecond TEXT NOT
> NULL, am_pm TEXT NOT NULL, admin BOOLEAN NOT NULL DEFAULT false, CONSTRAINT
> key PRIMARY KEY (registrar_id) ); CREATE TABLE IF NOT EXISTS user_name (
> user_name_id TEXT PRIMARY KEY REFERENCES registrar(registrar_id),
> first_name TEXT NOT NULL, middle_name TEXT NOT NULL, last_name TEXT NOT
> NULL);*
>
> When I enter the following pg commands :
>
>
> *INSERT INTO registrar VALUES (DEFAULT, '18551420601012L23', '1', '12',
> '2024', '18', '55', '14', '207', 'PM'); INSERT INTO user_name
> '18551420601012L23', 'John', 'Fitzgerald', 'Kennedy'); <= *
> *UserNameTable.write()*
>
> I get this message :
>
>
>
>
> *Exception in thread "main" java.sql.SQLException: In
> company.contact.Company$UserNameTable.write() ERROR: syntax error at or
> near "'18551420601012L23'"   Position: 23 *
>
> The error is in the user_name_id, which is a primary that references the
> table "*registrar*" 's "*registrar_id" *primary key.
> According to the documentation
> , the insertion
> should be OK, no?
>
> What am I doing wrong?
>

What happens when you run those INSERT commands directly in psql?

Hint: it's probably going to complain about a syntax error in the second
INSERT statement.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Help with syntax error

[Arbol One]

😳 O, I feel so dumb, that I find it so funny!!
Thanks man!!!

I need to step away from the computer for a while, I think fatigue is 
getting the best of me.


On 2024-12-01 7:17 p.m., Adrian Klaver wrote:

On 12/1/24 16:14, Arbol One wrote:

I have these tables:

*CREATE TABLE IF NOT EXISTS registrar (
index SERIAL,
registrar_id TEXT,
day TEXT NOT NULL,
month TEXT NOT NULL,
year TEXT NOT NULL,
hour TEXT NOT NULL,
minute TEXT NOT NULL,
second TEXT NOT NULL,
millisecond TEXT NOT NULL,
am_pm TEXT NOT NULL,
admin BOOLEAN NOT NULL DEFAULT false,
CONSTRAINT key PRIMARY KEY (registrar_id) );

CREATE TABLE IF NOT EXISTS user_name (
user_name_id TEXT PRIMARY KEY REFERENCES registrar(registrar_id),
first_name TEXT NOT NULL,
middle_name TEXT NOT NULL,
last_name TEXT NOT NULL);*

When I enter the following pg commands :

*INSERT INTO registrar VALUES (DEFAULT, '18551420601012L23', '1', 
'12', '2024', '18', '55', '14', '207', 'PM');
INSERT INTO user_name '18551420601012L23', 'John', 'Fitzgerald', 
'Kennedy'); <= **/UserNameTable.write()/*




You don't see the difference between the first and second statements.

Hint, it starts with V.


I get this message :

*/Exception in thread "main" java.sql.SQLException: In 
company.contact.Company$UserNameTable.write()

ERROR: syntax error at or near "'18551420601012L23'"
   Position: 23/
*

The error is in the user_name_id, which is a primary that references 
the table "*registrar*" 's "*registrar_id" *primary key.
According to the documentation 
, the 
insertion should be OK, no?


What am I doing wrong?


--
*/ArbolOne ™/*
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to 
providing free services to charitable organizations.
ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
progress [ í ]



--
*/ArbolOne ™/*
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing 
free services to charitable organizations.
ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
progress [ í ]

Re: Help with syntax error

[Adrian Klaver]

On 12/1/24 16:14, Arbol One wrote:

I have these tables:

*CREATE TABLE IF NOT EXISTS registrar (
index SERIAL,
registrar_id TEXT,
day TEXT NOT NULL,
month TEXT NOT NULL,
year TEXT NOT NULL,
hour TEXT NOT NULL,
minute TEXT NOT NULL,
second TEXT NOT NULL,
millisecond TEXT NOT NULL,
am_pm TEXT NOT NULL,
admin BOOLEAN NOT NULL DEFAULT false,
CONSTRAINT key PRIMARY KEY (registrar_id) );

CREATE TABLE IF NOT EXISTS user_name (
user_name_id TEXT PRIMARY KEY REFERENCES registrar(registrar_id),
first_name TEXT NOT NULL,
middle_name TEXT NOT NULL,
last_name TEXT NOT NULL);*

When I enter the following pg commands :

*INSERT INTO registrar VALUES (DEFAULT, '18551420601012L23', '1', '12', 
'2024', '18', '55', '14', '207', 'PM');
INSERT INTO user_name '18551420601012L23', 'John', 'Fitzgerald', 
'Kennedy'); <= **/UserNameTable.write()/*




You don't see the difference between the first and second statements.

Hint, it starts with V.


I get this message :

*/Exception in thread "main" java.sql.SQLException: In 
company.contact.Company$UserNameTable.write()

ERROR: syntax error at or near "'18551420601012L23'"
   Position: 23/
*

The error is in the user_name_id, which is a primary that references the 
table "*registrar*" 's "*registrar_id" *primary key.
According to the documentation 
, the 
insertion should be OK, no?


What am I doing wrong?


--
*/ArbolOne ™/*
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing 
free services to charitable organizations.
ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in 
progress [ í ]


--
Adrian Klaver
adrian.kla...@aklaver.com