Scripted Database Installation 03-01-2024

[victort]

victort submitted a new resource:

Scripted Database Installtion - script to install mariadb, mysql, or postgresql

This script will automate the installation MariaDB, MySQL, or PostgreSQL for you. All you need to do is define which one you prefer and set your preferred database user and database name.

Instructions and downlods are at https://github.com/tschettervictor/freebsd-database-scripts

Read more about this resource...

 

[dktech]

Thanks for sharing, I was hoping to run the PostgreSQL database in truenas jails as a project repository for DaVinci Resolve. So far, I have installed PostgreSQL according to your instructions, but when I use DaVinci Resolve to add the project library, I am prompted: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "192.168.1.165" and accepting TCP/IP connections on port 5432? QPSQL: Cannot connect

 

[victort]

By default it will only listen on localhost. To change this, edit the file at /var/db/postgres/data15/pga_hba.conf

Find the line at the bottom where it says

Code:

# IPv4 local connections:
host  all  all  127.0.0.1/32   trust

and change the address to either your IP or 0.0.0.0/0 to listen on everything.

 

[dktech]

Thank you very much for your quick reply, but the problem still exists after I operate in this way. Maybe I am not a programmer and don't know much about its operation, but I am sure that I have modified it successfully, and I have restarted the jails.

 

[victort]

Thank you very much for your quick reply, but the problem still exists after I operate in this way. Maybe I am not a programmer and don't know much about its operation, but I am sure that I have modified it successfully, and I have restarted the jails.

Does sockstat show postgresql listening on port 5432?

 

[dktech]

MARIADB=0
MYSQL=0
POSTGRESQL=1
DB_NAME="postgres"
DB_USER="postgres"
This is what I want to fill in here

 

[dktech]

Does sockstat show postgresql listening on port 5432?

Sorry, I don't understand what sockstat is

 

[victort]

Sorry, I don't understand what sockstat is

From the jail shell, type sockstat to see a list of running services.

 

[dktech]

 

[dktech]

View attachment 76633

 

[victort]

View attachment 76633

From this image it looks like it’s still not listening on the proper addresses.

Try to type service postgresql restart

 

[dktech]

From this image it looks like it’s still not listening on the proper addresses.

Try to type service postgresql restart

Documents installed with the system are in the /usr/local/share/doc/freebsd/
directory, or can be installed later with: pkg install en-freebsd-doc
For other languages, replace "en" with a language code like de or fr.

Show the version of FreeBSD installed: freebsd-version ; uname -a
Please include that output and any error messages when posting questions.
Introduction to manual pages: man man
FreeBSD directory layout: man hier

To change this login announcement, see motd(5).
root@165:~ # service postgresql restart
2024-03-17 00:15:40.947 CST [82327] LOG: ending log output to stderr
2024-03-17 00:15:40.947 CST [82327] HINT: Future log output will go to log destination "syslog".
root@165:~ #

 

[victort]

Documents installed with the system are in the /usr/local/share/doc/freebsd/
directory, or can be installed later with: pkg install en-freebsd-doc
For other languages, replace "en" with a language code like de or fr.

Show the version of FreeBSD installed: freebsd-version ; uname -a
Please include that output and any error messages when posting questions.
Introduction to manual pages: man man
FreeBSD directory layout: man hier

To change this login announcement, see motd(5).
root@165:~ # service postgresql restart
2024-03-17 00:15:40.947 CST [82327] LOG: ending log output to stderr
2024-03-17 00:15:40.947 CST [82327] HINT: Future log output will go to log destination "syslog".
root@165:~ #

And a sockstat again to see if it is listening on the right address. Also, try to remove the /24 from the address in the pg_hba.conf file.

 

[dktech]

And a sockstat again to see if it is listening on the right address. Also, try to remove the /24 from the address in the pg_hba.conf file.

There is no change, if deleted/24, it will prompt that the service cannot be started

 

[victort]

Can you try 0.0.0.0/0?

 

[dktech]

Can you try 0.0.0.0/0?

I have already tried it

 

[victort]

I have already tried it

Sorry about that. Change it back to what it was. I think it was 127.0.0.1/32

The file you need to edit is actually /var/db/postgres/data15/postgres.conf

Find these two lines,

Code:

#listen_addresses = 'localhost'
#port = 5432

(change listen_addresses to one of these three: '*' to listen on everything, 0.0.0.0 to listen on ipv4 only, and 192.168.x.x to listen on your IP

Uncomment (remove the # in front) the above lines then restart the service again.

EDIT: What I should have said above is that by default it will not listen on anything except the unix socket.

 

[dktech]

Sorry about that. Change it back to what it was. I think it was 127.0.0.1/32

The file you need to edit is actually /var/db/postgres/data15/postgres.conf

Find these two lines,

Code:

#listen_addresses = 'localhost'
#port = 5432

(change listen_addresses to one of these three: '*' to listen on everything, 0.0.0.0 to listen on ipv4 only, and 192.168.x.x to listen on your IP

Uncomment (remove the # in front) the above lines then restart the service again.

EDIT: What I should have said above is that by default it will not listen on anything except the unix socket.

Thank you very much for your reply, it worked this time,

 

[dktech]

Thank you very much for your help. At present, I can add the network project library to DaVinci Resolve, but I find that I can't backup the project library in DaVinci Resolve. I can optimize and copy it, but I can't backup it to my local hard disk. Is it because my user has no administrative rights? I look forward to hearing from you. The default user name when adding a project library is postgres and the password is DaVinci.

 

[victort]

Thank you very much for your help. At present, I can add the network project library to DaVinci Resolve, but I find that I can't backup the project library in DaVinci Resolve. I can optimize and copy it, but I can't backup it to my local hard disk. Is it because my user has no administrative rights? I look forward to hearing from you. The default user name when adding a project library is postgres and the password is DaVinci.

I don't think so. By default, the user that you specify gets ownership of the database and all permissions when it's created, and therefore will have all rights.