date:20190822


Hi list

On Windows, I have an unattended script that exports data overnight by 
issuing psql commands like :

psql -f mycommands.sql
whith mycommands.sql containing a bunch of \copy lines like :
\copy (SELECT * FROM someview) TO 
'\\windowsserver\windowsshare\somefile.csv'


When I run this script by hand, everything is fine but when it runs 
overnight it hangs and allways on the same line.
There is no lock in the database, and the connection is marked as active 
in the backend and running the statement "COPY (SELECT * FROM myview) TO 
STDOUT".

So where can things be hanging ?
My first guess (appart from db lock) was that the target file was locked 
by the filesystem. But wouldn't psql know about it and fail with an error ?


Thanks for your help !

--
Arnaud

Re: Importing from CSV, auto creating table?

2019-08-22 Thread Reid Thompson

On Wed, 2019-08-21 at 17:15 -0400, stan wrote:
> [EXTERNAL SOURCE]
> 
> 
> 
> I have a situation where we need to import data, as an interim measure,
> from spreadsheets.
> 
> I have read up on \copy and COPY, but I do not see that either of these can
> use the header from a CSV file to define a new table. Am I missing
> something?
> 
> Also downloaded something called pgfutter, which I thought would do this,
> but have not had any success with this. After I (thought) I had figured out
> the arguments, it just seams to hag forever.
> 
> 
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
> 
> 


https://csvkit.readthedocs.io/en/latest/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power

csvsql and sql2csv: ultimate power
...snip...
By default, csvsql will generate a create table statement for your data. You 
can specify what sort of database you are using with the -i flag:
...snip...

Re: Importing from CSV, auto creating table?

2019-08-22 Thread Reid Thompson

On Thu, 2019-08-22 at 08:25 -0400, Reid Thompson wrote:
> On Wed, 2019-08-21 at 17:15 -0400, stan wrote:
> > [EXTERNAL SOURCE]
> > 
> > 
> > 
> > I have a situation where we need to import data, as an interim measure,
> > from spreadsheets.
> > 
> > I have read up on \copy and COPY, but I do not see that either of these can
> > use the header from a CSV file to define a new table. Am I missing
> > something?
> > 
> > Also downloaded something called pgfutter, which I thought would do this,
> > but have not had any success with this. After I (thought) I had figured out
> > the arguments, it just seams to hag forever.
> > 
> > 
> > --
> > "They that would give up essential liberty for temporary safety deserve
> > neither liberty nor safety."
> > -- Benjamin Franklin
> > 
> > 
> 
> https://csvkit.readthedocs.io/en/latest/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power
> 
> csvsql and sql2csv: ultimate power
> ...snip...
> By default, csvsql will generate a create table statement for your data. You 
> can specify what sort of database you are using with the -i flag:
> ...snip...
> 

Example

https://stackoverflow.com/questions/35243432/how-to-generate-a-schema-from-a-csv-for-a-postgresql-copy

Re: Importing from CSV, auto creating table?

2019-08-22 Thread Reid Thompson

On Thu, 2019-08-22 at 09:01 -0400, Reid Thompson wrote:
> On Thu, 2019-08-22 at 08:25 -0400, Reid Thompson wrote:
> > On Wed, 2019-08-21 at 17:15 -0400, stan wrote:
> > > [EXTERNAL SOURCE]
> > > 
> > > 
> > > 
> > > I have a situation where we need to import data, as an interim measure,
> > > from spreadsheets.
> > > 
> > > I have read up on \copy and COPY, but I do not see that either of these 
> > > can
> > > use the header from a CSV file to define a new table. Am I missing
> > > something?
> > > 
> > > Also downloaded something called pgfutter, which I thought would do this,
> > > but have not had any success with this. After I (thought) I had figured 
> > > out
> > > the arguments, it just seams to hag forever.
> > > 
> > > 
> > > --
> > > "They that would give up essential liberty for temporary safety deserve
> > > neither liberty nor safety."
> > > -- Benjamin Franklin
> > > 
> > > 
> > 
> > https://csvkit.readthedocs.io/en/latest/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power
> > 
> > csvsql and sql2csv: ultimate power
> > ...snip...
> > By default, csvsql will generate a create table statement for your data. 
> > You can specify what sort of database you are using with the -i flag:
> > ...snip...
> > 
> 
> Example
> 
> https://stackoverflow.com/questions/35243432/how-to-generate-a-schema-from-a-csv-for-a-postgresql-copy
> 

a more detailed example

https://archive.is/pFpJi

Re: psql \copy hanging


On 8/22/19 1:47 AM, Arnaud L. wrote:

Hi list

On Windows, I have an unattended script that exports data overnight by 
issuing psql commands like :

psql -f mycommands.sql
whith mycommands.sql containing a bunch of \copy lines like :
\copy (SELECT * FROM someview) TO 
'\\windowsserver\windowsshare\somefile.csv'


When I run this script by hand, everything is fine but when it runs 
overnight it hangs and allways on the same line.
There is no lock in the database, and the connection is marked as active 
in the backend and running the statement "COPY (SELECT * FROM myview) TO 
STDOUT".

So where can things be hanging ?


The first thing I think of when seeing this sort of behavior on Windows 
is anti-virus software. Doing you have any running on the machine?


You say it always hangs on the same line. Is that line doing something 
different from the others? Say writing to a different location.





My first guess (appart from db lock) was that the target file was locked 
by the filesystem. But wouldn't psql know about it and fail with an error ?


Thanks for your help !

--
Arnaud






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

Re: psql \copy hanging


Le 22/08/2019 à 16:00, Adrian Klaver a écrit :

The first thing I think of when seeing this sort of behavior on Windows
is anti-virus software. Doing you have any running on the machine?

You say it always hangs on the same line. Is that line doing something
different from the others? Say writing to a different location.


Thanks for your insights Adrian.

There is no AV software on the machine as it is a Server version of 
Windows. (I know some people run AV on Windows Server, but I don't)


All the lines write to the same location which is a network share, but 
the hanging one is clearly the longest query to execute and the output 
file is one of the biggest one (though not the biggest).


Note that the share points to a local folder. The fact the psql writes 
to a share is only for robustness so that this script could be executed 
from any machine.


Cheers

--
Arnaud

Re: psql \copy hanging


On 8/22/19 7:07 AM, Arnaud L. wrote:

Le 22/08/2019 à 16:00, Adrian Klaver a écrit :

The first thing I think of when seeing this sort of behavior on Windows
is anti-virus software. Doing you have any running on the machine?

You say it always hangs on the same line. Is that line doing something
different from the others? Say writing to a different location.


Thanks for your insights Adrian.

There is no AV software on the machine as it is a Server version of 
Windows. (I know some people run AV on Windows Server, but I don't)


All the lines write to the same location which is a network share, but 
the hanging one is clearly the longest query to execute and the output 
file is one of the biggest one (though not the biggest).


The only thing I can think of at the moment is to put the offending line 
in a separate script and see what happens.




Note that the share points to a local folder. The fact the psql writes 
to a share is only for robustness so that this script could be executed 
from any machine.


Cheers

--
Arnaud




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

Re: psql \copy hanging


Le 22/08/2019 à 16:21, Adrian Klaver a écrit :

The only thing I can think of at the moment is to put the offending line
in a separate script and see what happens.


Then I think I'll first try to switch the line orders. I'll execute this 
action last and see if it changes anything. If it doesn't, I'll break 
the script into smaller parts.


Thanks for you help !

--
Arnaud

Re: psql \copy hanging

2019-08-22 Thread Jerry Sievers

"Arnaud L."  writes:

> Hi list
>
> On Windows, I have an unattended script that exports data overnight by
> issuing psql commands like :
> psql -f mycommands.sql
> whith mycommands.sql containing a bunch of \copy lines like :
> \copy (SELECT * FROM someview) TO
> '\\windowsserver\windowsshare\somefile.csv'
>
> When I run this script by hand, everything is fine but when it runs
> overnight it hangs and allways on the same line.

Is the overnight run being done as the same DB user you are testing
with?

If not, then perhaps the automated user deviates in resource settings
and/or permissions, in turn resulting in sluggish performance caused by
thrashing and/or inferior query plan.

> There is no lock in the database, and the connection is marked as
> active in the backend and running the statement "COPY (SELECT * FROM
> myview) TO STDOUT".
> So where can things be hanging ?
> My first guess (appart from db lock) was that the target file was
> locked by the filesystem. But wouldn't psql know about it and fail
> with an error ?
>
> Thanks for your help !
>
> --
> Arnaud
>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net

Support for using alias in having clause

2019-08-22 Thread Michael Lewis

Why can I use an alias for group by and order by and not for having? I am
just wondering if there is some barrier to it being implemented, or if it
just hasn't been.

select
table_schema || '.' || table_name AS schema_qualified_table_name,
count( column_name ) as column_count
from
information_schema.columns
group by
schema_qualified_table_name
having
count( column_name ) > 50 /* this works, but trying column_count > 50 does
not */
order by
column_count desc;

How to install Postgres 12 beta with YUM

I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
...

But when I try to find the version to install I do not see it, e.g. `yum 
list postgresql* | grep 12` doesn't show anything with version 12.


Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: How to install Postgres 12 beta with YUM


On 8/22/19 11:29 AM, Igal @ Lucee.org wrote:
I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=0


Not a YUM user, but what if you change enabled to 1?


gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
...

But when I try to find the version to install I do not see it, e.g. `yum 
list postgresql* | grep 12` doesn't show anything with version 12.


Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 




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

How to install Postgres 12 beta with YUM

I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
...

But when I try to find the version to install I do not see it, e.g. `yum 
list postgresql* | grep 12` doesn't show anything with version 12.


Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: How to install Postgres 12 beta with YUM


Adrian,

On 8/22/2019 12:22 PM, Adrian Klaver wrote:


On 8/22/19 11:29 AM, Igal @ Lucee.org wrote:
I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch 


enabled=0


Not a YUM user, but what if you change enabled to 1?


Thanks, that was indeed the issue (Joshua Drake pointed it to me offline 
earlier as it seemed that my emails are not posted to the list), but 
upon changing enabled to 1 and then running `yum update`, there is a new 
error:


failure: repodata/repomd.xml from pgdg12: [Errno 256] No more mirrors to 
try.
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/repodata/repomd.xml: 
[Errno 14] HTTPS Error 404 - Not Found


The XML data is missing.  If I change the URL to 11 then I get the XML 
file for Postgres 11.


Where should I report this issue?

Thank you,

Igal

Re: How to install Postgres 12 beta with YUM


On 8/22/19 12:35 PM, Igal @ Lucee.org wrote:

Adrian,

On 8/22/2019 12:22 PM, Adrian Klaver wrote:


On 8/22/19 11:29 AM, Igal @ Lucee.org wrote:
I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch 


enabled=0


Not a YUM user, but what if you change enabled to 1?


Thanks, that was indeed the issue (Joshua Drake pointed it to me offline 
earlier as it seemed that my emails are not posted to the list), but 
upon changing enabled to 1 and then running `yum update`, there is a new 
error:


failure: repodata/repomd.xml from pgdg12: [Errno 256] No more mirrors to 
try.
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/repodata/repomd.xml: 
[Errno 14] HTTPS Error 404 - Not Found


Yeah 12/ is not there:

https://download.postgresql.org/pub/repos/yum/

it is in:

https://download.postgresql.org/pub/repos/yum/testing/

You can file an issue here:

https://redmine.postgresql.org/projects/pgrpms

You will need a community account to file an issue:

https://www.postgresql.org/account/auth/4/



The XML data is missing.  If I change the URL to 11 then I get the XML 
file for Postgres 11.


Where should I report this issue?

Thank you,

Igal






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

Re: How to install Postgres 12 beta with YUM


On 8/22/19 12:35 PM, Igal @ Lucee.org wrote:

Adrian,

On 8/22/2019 12:22 PM, Adrian Klaver wrote:


On 8/22/19 11:29 AM, Igal @ Lucee.org wrote:
I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch 


enabled=0


Not a YUM user, but what if you change enabled to 1?


Thanks, that was indeed the issue (Joshua Drake pointed it to me offline 
earlier as it seemed that my emails are not posted to the list), but 
upon changing enabled to 1 and then running `yum update`, there is a new 
error:


failure: repodata/repomd.xml from pgdg12: [Errno 256] No more mirrors to 
try.
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/repodata/repomd.xml: 
[Errno 14] HTTPS Error 404 - Not Found


The XML data is missing.  If I change the URL to 11 then I get the XML 
file for Postgres 11.


Where should I report this issue?


In addition to my previous post maybe change:

baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch

to:

baseurl=https://download.postgresql.org/pub/repos/testing/yum/12/redhat/rhel-$releasever-$basearch



Thank you,

Igal






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

Re: How to install Postgres 12 beta with YUM


Adrian,

On 8/22/2019 12:48 PM, Adrian Klaver wrote:


In addition to my previous post maybe change:

baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch 



to:

baseurl=https://download.postgresql.org/pub/repos/testing/yum/12/redhat/rhel-$releasever-$basearch 



Almost!  "/testing" comes after "/yum" rather than before it, so it's

baseurl=https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-$releasever-$basearch

But before I figured that out I used the other link you posted and 
installed the 3 packages separately like so:


# yum install 
https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7.6-x86_64/postgresql12-libs-12beta3-1PGDG.rhel7.x86_64.rpm


# yum install 
https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7.6-x86_64/postgresql12-12beta3-1PGDG.rhel7.x86_64.rpm


# yum install 
https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7.6-x86_64/postgresql12-server-12beta3-1PGDG.rhel7.x86_64.rpm


Thanks for your help,

Igal

Having difficulties partitionning with jsonb

2019-08-22 Thread ouellet marc-andre

CREATE TABLE test ( id integer, data jsonb ) Partition by range (( data #>> 
'{info,time}' ));

CREATE TABLE test_part1 PARTITION OF test
FOR VALUES FROM ('3') TO ('4');

INSERT INTO test VALUES (1,'{"info":[{"time":39814.0,"value":2}, 
{"time":39815.0,"value":3}]}');

Is there a way to do this ? or is it in development?

Thanks!

Marc

How to use brin_summarize_range

2019-08-22 Thread Jeremy Finzel

Good afternoon!

I am finding it difficult to understand how to maintain my BRIN index from
the docs.  Specifically, this is the documentation on the
function brin_summarize_range which isn't clear to me:

brin_summarize_range(index regclass, blockNumber bigint) integer

   - summarize the page range covering the given block, if not already
   summarized

There is no information on how a user is to actually find blockNumber,
especially what blockNumber she might be interested in (like the end of the
table).  On my table, my BRIN index is all of a sudden all out of whack and
I'm trying to figure out why.  The planner doesn't choose it.  Even if I
force a BRIN scan, it estimates way wrong, and performs terribly.  I do not
have autosummarize on.  I am curious if vacuum somehow invalidated
everything?

When I ran brin_summarize_new_values, it immediately returned 0.  This
table has 292 million rows, and a straightforward insert-only pattern, but
we also prune data older than 1 year old. The BRIN index is on insert
time.  It was working great up until just a bit ago.

Any direction on using these brin functions would be very appreciated.

Thanks,
Jeremy

RE: Question on pgwatch

2019-08-22 Thread Bikram MAJUMDAR

Hi,
Need help from anyone in the team who has worked on pgwatch2.

Yesterday we installed  pgwatch2 docker image and started the container. 
We are running the pgwatch2 container from the database server itself -  
Now we want to add our databases for monitoring using the admin interface:
We are opening the pgwatch2 admin interface at :8080/dbs.But, when we 
try to add the database (cemtore) in the admin interface we get the following 
error:

Could not connect to specified host (ignore if gatherer daemon runs on another 
host): FATAL: no pg_hba.conf entry for host "172.17.0.2", user "cemtore", 
database "cemtore", SSL off

Any idea what we are doing wrong?

We would now like to add the database manually following notes below:
Usage
by default the pgwatch2 configuration database running inside Docker is being 
monitored so that you can immediately see some graphs, but you should add new 
databases by opening the admin interface at 127.0.0.1:8080/dbs or logging into 
the Postgres config DB and inserting into pgwatch2.monitored_db table (db - 
pgwatch2 , default user/pw - pgwatch2/pgwatch2admin)

But, my question is, how do I run the psql on my  database server to login to 
this default pgwatch2 configuration DB?

Bikram Majumdar
Sr Software Developer/DBA, Aqsacom Inc.
c. 1.972.365.3737

Disclaimer. This electronic mail message contains CONFIDENTIAL information 
which is (a) CUSTOMER / SUPPLIER / PARTNER PRIVILEGED COMMUNICATION, WORK 
PRODUCT, PROPRIETARY IN NATURE, OR OTHERWISE PROTECTED BY LAW FROM DISCLOSURE, 
and (b) intended only for the use of the Addressee(s) named herein. If you are 
not an Addressee, or the person responsible for delivering this to an 
Addressee, you are hereby notified that reading, copying, or distributing this 
message is prohibited. If you have received this electronic mail message in 
error, please reply to the sender and take the steps necessary to delete the 
message completely from your computer system. Statement and opinions expressed 
in this e-mail are those of the sender, and do not necessarily reflect those of 
AQSACOM. AQSACOM accepts no liability for any damage caused by any virus/worms 
transmitted by this email.

-Original Message-
From: Bikram MAJUMDAR 
Sent: Friday, August 16, 2019 4:43 PM
To: Luca Ferrari ; pgsql-general 

Subject: RE: Question on pgwatch

Hi Luca,

I have installed docker  (yum install ) on my linux server.  Does it already 
come with the pgwatch public image?  How do I download and install pgwatch 
docker image from github -- 

Thanks and regards
Bikram Majumdar
Sr Software Developer/DBA, Aqsacom Inc.
c. 1.972.365.3737

Disclaimer. This electronic mail message contains CONFIDENTIAL information 
which is (a) CUSTOMER / SUPPLIER / PARTNER PRIVILEGED COMMUNICATION, WORK 
PRODUCT, PROPRIETARY IN NATURE, OR OTHERWISE PROTECTED BY LAW FROM DISCLOSURE, 
and (b) intended only for the use of the Addressee(s) named herein. If you are 
not an Addressee, or the person responsible for delivering this to an 
Addressee, you are hereby notified that reading, copying, or distributing this 
message is prohibited. If you have received this electronic mail message in 
error, please reply to the sender and take the steps necessary to delete the 
message completely from your computer system. Statement and opinions expressed 
in this e-mail are those of the sender, and do not necessarily reflect those of 
AQSACOM. AQSACOM accepts no liability for any damage caused by any virus/worms 
transmitted by this email.

-Original Message-
From: Luca Ferrari  
Sent: Friday, August 16, 2019 7:24 AM
To: Bikram MAJUMDAR ; pgsql-general 

Subject: Re: Question on pgwatch

On Wed, Aug 14, 2019 at 5:10 PM Bikram MAJUMDAR  
wrote:
> From where did you download docker for linux,  and , the docker with the 
> pgwatch container?
> And, any installation/configuration tips for pgwatch running on linux?

I'm not sure what you are effectively askin to me, however the starting point 
for bth projects are the related web sites:
- docker 
- pgwatch  and here you can find detailed instruction 
from dowloading to run it 

Luca

database files are incompatible with server

I have a data directory that was created by Postgres 12 (I thought beta 
3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to set 
the cluster to the same PGDATA.  I have set the owner of the directory 
to postgres:postrgres, and the permissions to 0700, but I'm getting the 
following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


How can I start up the Cluster?

Is version 201906161 beta 2?

Would running PG12 beta 2 work?

Would running initdb destroy the data?

Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: database files are incompatible with server

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought beta
3 but now am not sure anymore) running in Docker.

I have installed Postgres 12b3 as a Systemd service and am trying to set
the cluster to the same PGDATA. I have set the owner of the directory
to postgres:postrgres, and the permissions to 0700, but I'm getting the
following error:

2019-08-22 23:40:48.759 UTC [23044] FATAL: database files are
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL: The database cluster was
initialized with CATALOG_VERSION_NO 201906161, but the server was
compiled with CATALOG_VERSION_NO 201907221.

So the cluster is from an older version then the server.

How can I start up the Cluster?

Is version 201906161 beta 2?

Yes:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139

Would running PG12 beta 2 work?

Worth a try.

Would running initdb destroy the data?

initdb will not run on a directory with files in it:

bin/initdb -D /usr/local/pgsql11_dev/
The files belonging to this database system will be owned by user
"postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

initdb: directory "/usr/local/pgsql11_dev" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/usr/local/pgsql11_dev" or run initdb
with an argument other than "/usr/local/pgsql11_dev".

Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org

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

Re: database files are incompatible with server

2019-08-22 Thread Ron


On 8/22/19 7:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought beta 3 
but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to set 
the cluster to the same PGDATA.  I have set the owner of the directory to 
postgres:postrgres, and the permissions to 0700, but I'm getting the 
following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.


Aren't Pg files supposed to be compatible within minor versions?

--
Angular momentum makes the world go 'round.

Re: database files are incompatible with server


On 8/22/19 6:29 PM, Ron wrote:

On 8/22/19 7:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought 
beta 3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to 
set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but I'm 
getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.


Aren't Pg files supposed to be compatible within minor versions?



Betas are a moving target so that does not hold:

https://www.postgresql.org/developer/beta/

"Features are subject to changes that are backwards incompatible at any 
time during the development of the betas, and could possibly be removed 
altogether."




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

Re: database files are incompatible with server

2019-08-22 Thread Ron


On 8/22/19 8:49 PM, Adrian Klaver wrote:

On 8/22/19 6:29 PM, Ron wrote:

On 8/22/19 7:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought beta 
3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to 
set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but I'm 
getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.


Aren't Pg files supposed to be compatible within minor versions?



Betas are a moving target so that does not hold:

https://www.postgresql.org/developer/beta/

"Features are subject to changes that are backwards incompatible at any 
time during the development of the betas, and could possibly be removed 
altogether."


Features as in the on-disk structure?

--
Angular momentum makes the world go 'round.

Re: database files are incompatible with server


Adrian,

On 8/22/2019 5:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought 
beta 3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to 
set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but I'm 
getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.


How can I start up the Cluster?

Is version 201906161 beta 2?


Yes:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139 



Thanks for the link.  It's good to know where these things are stored.

Thank you,

Igal

Re: database files are incompatible with server


On 8/22/19 7:12 PM, Ron wrote:

On 8/22/19 8:49 PM, Adrian Klaver wrote:

On 8/22/19 6:29 PM, Ron wrote:

On 8/22/19 7:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought 
beta 3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying 
to set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but 
I'm getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster 
was initialized with CATALOG_VERSION_NO  201906161, but the server 
was compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.


Aren't Pg files supposed to be compatible within minor versions?



Betas are a moving target so that does not hold:

https://www.postgresql.org/developer/beta/

"Features are subject to changes that are backwards incompatible at 
any time during the development of the betas, and could possibly be 
removed altogether."


Features as in the on-disk structure?



Short version:
T
he error was about the catversion which tracks the system catalog state. 
Adding/subtracting features can change that state and make clusters 
incompatible.


Long version:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139



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

Re: database files are incompatible with server


On 8/22/2019 5:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought 
beta 3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to 
set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but I'm 
getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.



How can I start up the Cluster?

Is version 201906161 beta 2?


Yes:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139 



Would running PG12 beta 2 work?


Worth a try.


That worked.  I used the Docker image postgres:12-beta2 to bring up the 
server.


One issue that I kept having was error messages like chown: changing 
ownership of '...': Permission denied.  That turned out to be an SELinux 
issue on the CentOS machine.  I have set SELinux to permissive mode 
temporarily to bring the server up.  Sharing it here for the next guy 
(most likely my future self).


Thanks again,

Igal

Re: database files are incompatible with server

2019-08-22 Thread Tom Lane

Adrian Klaver  writes:
> On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
>> How can I start up the Cluster?
>> Is version 201906161 beta 2?

> Yes:
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139

FWIW, if you have both beta2 and beta3 executables at hand, it should
work to do a pg_upgrade to convert the cluster to beta3.

regards, tom lane

Re: database files are incompatible with server


On 8/22/2019 9:15 PM, Tom Lane wrote:

Adrian Klaver  writes:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:

How can I start up the Cluster?
Is version 201906161 beta 2?



Yes:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139

FWIW, if you have both beta2 and beta3 executables at hand, it should
work to do a pg_upgrade to convert the cluster to beta3.


Good to know!

Thank you,

Igal

Re: psql \copy hanging