Configuring PostgreSQL v9.3 on Synology NAS

[luerman]

I've seen a couple threads mentioning the possibility of using PostgreSQL on a Synology NAS, but no info on configuring. I have a Synology DS1512+ NAS with DSM v5.1-5004 (2014-11-06), which includes PostgreSQL v9.3.5. After some probing/configuring, I've settled on the following changes, but am getting an error when trying to create the database that I need some guidance on. I am using "Photo Supreme Server Edition - PostgreSQL 2.2.5.1045".

On the NAS, the postgres user is already defined without a password. And PhotoSupreme appears to require a Superuser password. But I don't want to change the postgres user configuration, because I'm not sure how that might affect other functionality on the NAS. So, I started by defining a new superuser: photosupreme
1. Logged in as "admin"
2. Created userid: "/usr/syno/pgsql/bin/createuser --pwprompt --superuser photosupreme"
3. and entered password twice when prompted.

Next, I configured pg_hba.conf and postgresql.conf. These are located in /etc/postgresql.
1. Logged in as "root".
2. "vi /etc/postgresql/pg_hba.conf" and added the following: "host all photosupreme,admin samenet md5". With this, I think I've given admin and photosupreme userids access to all databases from any PC on my local network.
3. "vi /etc/postgresql/postgresql.conf" and made these changes/additions as recommended in the PhotoSupreme install doc:
3a) listen_addresses="*" (allow access from all addresses - assuming pg_hba.conf will control access)
3b) shared_buffers=768MB (25% of 3GB memory in NAS)
3c) effective_cache_size=1536MB (50% of 3GB memory in NAS)
3d) work_mem=32MB

With these settings, selecting "Test Settings" with "Super User Name : photosupreme" a password successfully completes with this message "Connection settings are tested ok."

But when selecting "OK" to create a new database, it fails with this result:

"TPostgreSQLConnection: Creation or dropping of database failed (PostgreSQL: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII).
HINT: Use the same encoding as in the template database, or use template0 as template.
Severity: ERROR
SQL State: 22023
Primary Error: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT: Use the same encoding as in the template databse, or use template0 as template.).
Press OK to ignore and risk data corruption.
Press Cancel to kill the program."

I pressed Cancel.

I assume that PhotoSupreme is trying to create the database with UTF8 encoding resulting in this error. What can I do to avoid this error? Can I manually define the database? Or can something be configured in PhotoSupreme prior to attempting to create the database to work around this?

Any advice?

Thanks

[Hert]

Very interesting development and I am so curious if anyone will eventually be able to run Photo Supreme on a NAS. There are QNAP and Synology NAS that allow running PostgreSQL and so if this can be configured to run then that would be great news as it then offers a true multi user environment for home or small office usage using a fairly cheap and easy environment.

I can't help you with details as I'm not a PostgreSQL database expert myself, but the script that is ran to create the database can be found in the installation folder, resources, sqlscripts. On Mac that is inside the application package, resources, sqlscripts (of the top of my head). But UTF8 is a PSU requirement (though maybe not for you if you never use any special characters) and so better to find a solution so that the NAS allows the creation of a UTF8 database.

[luerman]

I couldn't find anything in the sqlscripts that looked like it was going to help in this case, so I did some internet searching and found a couple of links with instructions about this problem.
http://stackoverflow.com/questions/1673 ... compatible
http://www.pebra.net/blog/2013/06/10/wh ... ash-latin/
https://github.com/cyclestreets/nominat ... /issues/10

I did the following and have successfully created the database:
su postgres
psql
postgres=#update pg_database set datistemplate=false where datname='template1';
(response) UPDATE 1
postgres=# drop database template1;
(response) DROP DATABASE
postgres=# create database template1 with owner=postgres template=template0 encoding='UTF-8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8';
(response) CREATE DATABASE
postgres=# update pg_database set datistemplate=true where datname='template1';
(response) UPDATE 1

I restarted the NAS (for the heck of it).

And then PhotoSupreme successfully created the database:
"Your database is created. Use the username admin and password master2004 to access the application.
Write this password down. ..."

Then I attempted to login, and got this message:
"Unable to connect to the database.
The database reports:
Connection to database failed (PostgreSQL:FATAL:no pg_hba.conf entry for host "xxx.xxx.x.x", user "idimager_main", database "photosupreme".

So, I updated the pg_hba.conf entry to include idimager_main as a user:
"host all photosupreme,admin,idimager_main samenet md5"

And restarted the NAS (probably could have simply restarted PostgreSQL), and again attempted to log into PhotoSupreme. I got to the "Your database is being updated. This may take a while to complete..."

And then it failed:
"Unable to connect to the database.
The database reports TPostgreSQLConnection: Execution of query failed (PostgreSQL:
ERROR: text search configuration "english" does not exist
LINE1: ...ContentType on idSearchData using
GIN(to_tsvector('english',...

Severity: ERROR
SQL State: 42704
Primary Error: text search configuration "english" does not exist Character: 80).

Press OK to ignore...
Press Cancel to kill the program."

I selected Cancel.

I'll get back to this tomorrow (hopefully)... Gotta go for now.

[Hert]

I'm impressed at how far you already got with this. You're just a fraction away from getting this to work and I'm excited.

From the error, I assume that the default English dictionary doesn't exist in the PostgreSQL installation for the Synology. It should be easy to create, but maybe there's an addin available that installs the dictionary (better than creating an empty one)

http://www.postgresql.org/docs/9.1/stat ... aries.html

[luerman]

Just a quick update for now:

Reviewing some Synology posts, it looks like I probably shouldn't be messing with the installed postgres instance. There are further hints that I can install another instance of postgres. I'll be looking into that.

For now, I'm backing out the changes I've previously described.

If I make any further progress, I'll come back and update this thread.

[weidmic]

Any update on this? I am getting a synology ds1513+ and would love to have the DB running on the NAS...
But before I am digging into all this I like to see how possible and reasonable it is

Best Regards,
Michael

[HCS]

I have tried to do the same, but backed out of the idea after finding out how "not so straightforward" it is

Perhaps the following thread on this forum gives some further insight in the configuration: http://forum.idimager.com/viewtopic.php?f=57&t=23689

[weidmic]

Thanks for the link HCS,
I'll see if I can get it to run as soon I have the NAS setup.

Regards,
Michael

[Hert]

I'm curious if you can get this to work, Michael... Please keep us posted

[weidmic]

luerman,

when I try to create a new user I get:
could not connect to database postgres: FATAL: role "admin" does not exist
to create the new user I used:
/usr/syno/pgsql/bin/createuser --pwprompt --superuser photosupreme
and I also tried:
/usr/syno/pgsql/bin/createuser -P photosiupreme

Have you seen that too?

I did also install phpPgAdmin on the synology drive but cant login to the DB yet.
here I get the error: Login disallowed for security reasons

Damn... Right now I am stuck

[we_are_borg]

luerman,

when I try to create a new user I get:
could not connect to database postgres: FATAL: role "admin" does not exist
to create the new user I used:
/usr/syno/pgsql/bin/createuser --pwprompt --superuser photosupreme
and I also tried:
/usr/syno/pgsql/bin/createuser -P photosiupreme

Have you seen that too?

I did also install phpPgAdmin on the synology drive but cant login to the DB yet.
here I get the error: Login disallowed for security reasons

Damn... Right now I am stuck

Login in under root with SSH

Code: Select all

su - postgres

If you don't su you will get errors.

Code: Select all

createuser -d -a -P USERNAME
[code]

USERNAME make it a good one after that enter password two times

[code]
createdb DATABASENAME

DATABASENAME the name you want for the database.


After this you are done you can now access PostgreSQL with phpPgAdmin you also have access to the Synology database do not alter anything there unless you know what you're doing.

[weidmic]

Thanks, that worked... (almost)

I had to login as root - then I could su - as admin I got the message:

Code: Select all

su: must be suid to work properly

Then I created a DB and a User and now I am able to login to postgres via phpPgAdmin.

Thanks again!
Michael

[we_are_borg]

Thanks, that worked... (almost)

I had to login as root - then I could su - as admin I got the message:

Code: Select all

su: must be suid to work properly

Then I created a DB and a User and now I am able to login to postgres via phpPgAdmin.

Thanks again!
Michael

You are right it should have been root not admin i have edited the above message accordingly.

No problem i was looking for a solution to to get in but it toke me 2 hours before i knew enough to get in.

[weidmic]

we_are_borg

I have another question.
No problems getting in with phpPgAdmin that has been installed on my NAS... so far so good
I have installed pgAdmin III for windows - I can log into postgres only if I tunnel via ssh 22
otherwise I get:

Code: Select all

FATAL: no pg_hba.conf entry for host "192.168.1.100", user "PSuser", database "postgres"

I did edit the pg_hba.conf and added the line

Code: Select all

host all all 192.168.1.0/24 md5 "

which should allow for 192.168.1.0 til 192.168.1.255

I did also edit the postgressql.conf to listen to all adresses

Code: Select all

listen_addresses = '*'

However, I still have no luck to connect to to the database via host (192.168.1.100)

Do you have any idea what I am doing wrong?
I have spent many hours already but dont get any further

Cheers,
Michael

[HCS]

we_are_borg

I have another question.
No problems getting in with phpPgAdmin that has been installed on my NAS... so far so good
I have installed pgAdmin III for windows - I can log into postgres only if I tunnel via ssh 22
otherwise I get:

Code: Select all

FATAL: no pg_hba.conf entry for host "192.168.1.100", user "PSuser", database "postgres"

I did edit the pg_hba.conf and added the line

Code: Select all

host all all 192.168.1.0/24 md5 "

which should allow for 192.168.1.0 til 192.168.1.255

I did also edit the postgressql.conf to listen to all adresses

Code: Select all

listen_addresses = '*'

However, I still have no luck to connect to to the database via host (192.168.1.100)

Do you have any idea what I am doing wrong?
I have spent many hours already but dont get any further

Cheers,
Michael

Did you by any chance type the listen_addresses yourself?

When i did that (i'm on a Mac), it didn't work, because apparently it then has some formatting. Later, i copied from the example text in postgresql.conf and suddenly the "same" entry worked.

Perhaps you can give that a try?