Обсуждение: How to get PG 9.3 for a RaspberryPI (Debian Wheezy)?

Hi,
how can I get a working PG 9.3 for a RaspberryPI (Debian Wheezy)?
In the Raspian repository is only a V9.1 and the Debian repo of
postgresql.org has no ARM binaries.

I found a howto that describes how to fetch the source from
postgresql.org, compile it and set up a local repository with those
homecooked deb-packages.

I compiled those packages:
postgresql-9.3
postgresql-common
postgresql-client-common
pgdg-keyring

Then I installed: postgresql-9.3 postgresql-client-9.3
postgresql-contrib-9.3 postgresql-common postgresql-client-common.

At first I could install PG 9.3 and connect with pgAdmin from another
machine as well as with psql on the local console.
The server is set to listen on 192.168.0.2 and 127.0.0.1.

When I tried a small PHP-PDO test file on a local apache2 it ran in a
timeout as I tried to reach the local PG cluster. A PG on another box
was reachable though.
So PHP PDO should be basically OK.

When I checked the logfile of the local PG-9.3 server I found an error
on startup that complained it couldn't bind a IPv4 socket for 5432.
Another error says autovauum is not running.
The server started in the end though and accepted connections.

There seems to be a socket file in /var/run/postgresql that appeares and
disapperes when I start/stop the PG service.

Someone wrote in a forum to purge the whole cluster and just reinstall it.
I tried this but then aptitude can't rebuild the cluster. Now it rejects
the whole installation because of the missing net socket when it tries
to initialize the cluster.

I then even tried to remove the local repository and install PG 9.1.
That doesn't work either now. Same problem with the socket.

Is there a way to get a working PG9.3 on a Rasberry?


regards
Andreas


PS: Yeah, I do understand a Raspberry is not really an enterprise grade
machine and rather on the lower end of everything but I only want to
have it to get the hang of some web design stuff.

On Tue, Aug 5, 2014 at 12:32 PM, Andreas <maps.on@gmx.net> wrote:
> I then even tried to remove the local repository and install PG 9.1.
> That doesn't work either now. Same problem with the socket.
> Is there a way to get a working PG9.3 on a Rasberry?
This seems like a problem inherent to your OS or your RPMs as two
buildfarm machines are Raspberry PIs and are able to run Postgres:
hamster and chipmunk. So I don't see any reason why Postgres would not
run on it. Why don't you give a try to Raspbian? You could even update
their packages to 9.3:
http://www.raspberryconnect.com/raspbian-packages-list/item/56-raspbian-database
Regards,
--
Michael

Am 05.08.2014 um 06:32 schrieb Michael Paquier:
> On Tue, Aug 5, 2014 at 12:32 PM, Andreas <maps.on@gmx.net> wrote:
>> Is there a way to get a working PG9.3 on a Rasberry?

> This seems like a problem inherent to your OS or your RPMs as two
> buildfarm machines are Raspberry PIs and are able to run Postgres:
> hamster and chipmunk. So I don't see any reason why Postgres would not
> run on it. Why don't you give a try to Raspbian? You could even update
> their packages to 9.3:
> http://www.raspberryconnect.com/raspbian-packages-list/item/56-raspbian-database


As I mentioned, I have the current Raspian Image which is actually a
Debian Wheezy. In the Repository is only PG 8.4 and 9.1.
I'd rather have 9.3 to play with the new features.

I don't see a 9.3 in the link you provided.
Where did you find the upgrade there?


Andreas

Am 05.08.2014 um 09:36 schrieb Giuseppe Broccolo:
>
> Maybe the content of this blog could be usefull: http://raspberrypg.org/


Hi Guiseppe,

I found this (your) Blog with google.  :)
In the post how to install PG you just include the postgresql.org
repository and fetch the binary from there.
Probaply they dropped the binary support because there is no ARM stuff
anymore as the user Bill wrote on 27.12.2013.

Actually I used another answer in the Blog to compile PG 9.3.
I did it like Rafael described it here:
http://raspberrypg.org/2013/11/step-5-installing-postgresql-on-my-raspberry-pi/

It took about 2-3 hours to compile and all provided test ran through.
Then I installed it and I could connect to the server at first.
Only the next day when I tried to reach it from the local apache&php the
connect timed out. Thats when I discovered the error about the socket in
the log.

If you can get it going it would be utterly great if you wrote an howto
in your blog.   :)


Regards
Andreas

Andreas <maps.on@gmx.net> writes:
> When I checked the logfile of the local PG-9.3 server I found an error
> on startup that complained it couldn't bind a IPv4 socket for 5432.
> Another error says autovauum is not running.

It would help if you provided the exact log messages, but what this sounds
like is a networking configuration problem.  Do you have DNS service on
this machine, and if so does it correctly resolve "localhost"?

There has been some talk in the past about kluging things so that PG would
work even if "localhost" didn't resolve, but the case hasn't seemed worth
putting much effort into.

            regards, tom lane

Am 08.08.2014 um 01:20 schrieb Tom Lane:
> Andreas <maps.on@gmx.net> writes:
>> When I checked the logfile of the local PG-9.3 server I found an error
>> on startup that complained it couldn't bind a IPv4 socket for 5432.
>> Another error says autovauum is not running.
>
> It would help if you provided the exact log messages, but what this sounds
> like is a networking configuration problem.  Do you have DNS service on
> this machine, and if so does it correctly resolve "localhost"?
>
> There has been some talk in the past about kluging things so that PG would
> work even if "localhost" didn't resolve, but the case hasn't seemed worth
> putting much effort into.

Hi Tom,
I guess I partly figured it out.

1)   The big issue was that when I sat up eth0 to a fixed IP, I
accidentally commented out the config line for LO. Therefore there was
no known protocol for the socket and PG couldn't bind it to localhost.

2)   It appeares that when LO is set to INET (IPv4) it doesn't work as
IPv6. On the other hand PG with the provided postgresql.conf tries to
listen on "localhost" and interprets this as an IPv6 address.
This prohibits the 1'st generation of the cluster while initial
installation through aptitude.
After changing the listen_adresses to "127.0.0.1" I could generate the
cluster and PG works.

3)   There still is some minor glitch in the startup of the PG service.
Even with a complete new install of Raspian + dist-upgrade and
installation of postgres-9.1 from the repository, I get a line like:
"CEST LOG:  incomplete startup packet"
in the log, everytime I start up PG. It doesn't repeat later.
This doesn't go away with the self compiled PG 9.3.5 so it may rather be
triggered by something in Raspian.

Otherwise PG seems to run now ... well rather "move" as it's on a 700MHz
singlecore ARM   :}

Do you have a suggestion about #3 ?


Regards
Andreas

I guess this is easy a question for the gurus, but afraid I cannot get te answer!

I have this table:

aif_test=# \d sbox;
             Table "public.sbox"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | character varying(255) | not null
 data   | lo                     |
Indexes:
    "sbox_pkey" PRIMARY KEY, btree (id)


And trying to insert a binary file which I show only the first lines:

Emitidas/10001.doc|\\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C6E733D22687474703A2F2F6366652E646769
2E6775622E75792220786D6C6E733A6E73323D22687474703A2F2F7777772E77332E6F72672F323030302F30392F786D6C64736967232220786D6C6E733A7873693D22687474703A2F2F7777772E77332E6F726
72F323030312F584D4C536368656D612D696E7374616E6365222076657273696F6E3D22312E3

When runing a simple insert I get:

[postgres@dbfactura01dmz  /tmp]$ psql -d aif_test -U aif < subida_postgres_copys_sbox.sql
Password for user aif:
ERROR:  invalid input syntax for type oid: \x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C6E733D22687474703A2F2F6366652E6.............

sql script:
[postgres@dbfactura01dmz  /tmp]$ more subida_postgres_copys_sbox.sql
COPY sbox FROM '/tmp/TXT/SBOX.txt' WITH DELIMITER '|' NULL '';

I am not able to insert a simple binary text into a "lo" type column!!!  Read on the doc but it should work thoug. Any help please??

On 08/12/2014 06:20 AM, Jose Moreira wrote:
> I guess this is easy a question for the gurus, but afraid I cannot get
> te answer!
>
> I have this table:
>
> aif_test=# \d sbox;
>               Table "public.sbox"
>   Column |          Type          | Modifiers
> --------+------------------------+-----------
>   id     | character varying(255) | not null
>   data   | lo                     |
> Indexes:
>      "sbox_pkey" PRIMARY KEY, btree (id)
>
>
> And trying to insert a binary file which I show only the first lines:
>
>
Emitidas/10001.doc|\\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C6E733D22687474703A2F2F6366652E646769
>
2E6775622E75792220786D6C6E733A6E73323D22687474703A2F2F7777772E77332E6F72672F323030302F30392F786D6C64736967232220786D6C6E733A7873693D22687474703A2F2F7777772E77332E6F726
> 72F323030312F584D4C536368656D612D696E7374616E6365222076657273696F6E3D22312E3
>
> When runing a simple insert I get:
>
> [postgres@dbfactura01dmz  /tmp]$ psql -d aif_test -U aif <
> subida_postgres_copys_sbox.sql
> Password for user aif:
> ERROR:  invalid input syntax for type oid:
>
\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C6E733D22687474703A2F2F6366652E6.............
>
> sql script:
> [postgres@dbfactura01dmz  /tmp]$ more subida_postgres_copys_sbox.sql
> COPY sbox FROM '/tmp/TXT/SBOX.txt' WITH DELIMITER '|' NULL '';
>
> I am not able to insert a simple binary text into a "lo" type column!!!
> Read on the doc but it should work thoug. Any help please??

I think the type you are looking for is bytea:

http://www.postgresql.org/docs/9.0/interactive/datatype-binary.html

If you really want to use large objects(lo) see here:

http://www.postgresql.org/docs/9.0/interactive/largeobjects.html

>
>
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Jose Moreira wrote:

> I guess this is easy a question for the gurus, but afraid I cannot get te answer!
> 
> I have this table:
> 
> aif_test=# \d sbox;
>              Table "public.sbox"
>  Column |          Type          | Modifiers
> --------+------------------------+-----------
>  id     | character varying(255) | not null
>  data   | lo                     |
> Indexes:
>     "sbox_pkey" PRIMARY KEY, btree (id)
> 
> 
> And trying to insert a binary file which I show only the first lines:
> 
> Emitidas/10001.doc|\\x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C
> 41434B43464520786D6C6E733D22687474703A2F2F6366652E646769
> 2E6775622E75792220786D6C6E733A6E73323D22687474703A2F2F7777772E77332E6F72672F323030302F30392F786D6C6473
> 6967232220786D6C6E733A7873693D22687474703A2F2F7777772E77332E6F726
> 72F323030312F584D4C536368656D612D696E7374616E6365222076657273696F6E3D22312E3
> 
> When runing a simple insert I get:
> 
> [postgres@dbfactura01dmz  /tmp]$ psql -d aif_test -U aif < subida_postgres_copys_sbox.sql
> Password for user aif:
> ERROR:  invalid input syntax for type oid:
> \x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C41434B43464520786D6C
> 6E733D22687474703A2F2F6366652E6.............
> 
> sql script:
> [postgres@dbfactura01dmz  /tmp]$ more subida_postgres_copys_sbox.sql
> COPY sbox FROM '/tmp/TXT/SBOX.txt' WITH DELIMITER '|' NULL '';
> 
> I am not able to insert a simple binary text into a "lo" type column!!!  Read on the doc but it should
> work thoug. Any help please??

You must be using the "lo" extension.

Reading the doc I see that "lo" is just an alias for "oid", so that is not the way to
insert a large object.

You need to either import a file with lo_import() or use lo_create() and lowrite() to
create a large object. See http://www.postgresql.org/docs/current/static/largeobjects.html .

The extension essentially contains a trigger that you can use to automatically
delete the large object if the row gets deleted in the table.

Maybe you should go for the "bytea" data type, that would work as you expect.

Yours,
Laurenz Albe