tutorials:mairlistdb:setup-postgresql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
tutorials:mairlistdb:setup-postgresql [2014/10/01 08:30] – [Windows] tw | tutorials:mairlistdb:setup-postgresql [2019/07/09 16:18] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 5: | Line 5: | ||
PostgreSQL is an open source database management system, available for Windows, Linux and other operating systems. Being free of charge, fast and networking-enabled, | PostgreSQL is an open source database management system, available for Windows, Linux and other operating systems. Being free of charge, fast and networking-enabled, | ||
- | ====== Downloading and Installing | + | ===== PostgreSQL |
- | ===== Windows | + | ==== Windows ==== |
Binary packages/ | Binary packages/ | ||
Line 15: | Line 15: | ||
The Windows installer includes the graphical administration frontend, **pgAdmin III**. | The Windows installer includes the graphical administration frontend, **pgAdmin III**. | ||
- | ==== Installation | + | === Installation === |
Download the appropriate setup file for your system (in this example '' | Download the appropriate setup file for your system (in this example '' | ||
Line 42: | Line 42: | ||
</ | </ | ||
- | ==== Creating User and Database ==== | + | === Create user and database |
- | ===== Linux ===== | + | The next step is to create an SQL user and an empty database for mAirList. |
- | PostgreSQL is available as a package in all major Linux distributions. | + | Open pgAdmin III, the main window will show: |
+ | |||
+ | {{ : | ||
+ | |||
+ | In the tree on the left, you see the " | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Enter the password and click OK. The database tree will expand and show a couple of new nodes: Databases, Tablespaces, | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | First, we have to create a new user (" | ||
+ | |||
+ | In the dialog that appears, first enter the desired name of the role (e.g. // | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Then switch to the // | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Click OK, and the login role will be created. | ||
+ | |||
+ | Next, we create an empty database in which mAirList will store its contents later. Right-click the // | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Leave all other fields untouched. In particular, the encoding of the database (on the // | ||
+ | |||
+ | === Enable network access === | ||
+ | |||
+ | Out of the box, PostgreSQL does not allow any connections from other computers in your LAN. Before we can access the server from another computer, we must edit to configuration files -- '' | ||
+ | |||
+ | These configuration files can be found in the PostgreSQL data directory you chose during setup - by default this is '' | ||
+ | |||
+ | You can open the files with any text editor, or use the editing functions built into pgAdmin III. We will demostrate the latter option here. | ||
+ | |||
+ | == Edit postgresql.conf == | ||
+ | |||
+ | In pgAdmin, go to //File -> Open postgresql.conf//, | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Look for the setting name // | ||
+ | |||
+ | Note: Newer PostgreSQL installations on Windows apparently have this enabled by default. It's a good idea to confirm that the setting is correct though. | ||
+ | |||
+ | Don't forget to click Save if you have made any changes. | ||
+ | |||
+ | == Edit pg_hba.conf == | ||
+ | |||
+ | The second file, '' | ||
+ | |||
+ | In pgAdmin, click //File -> Open pg_hba.conf//, | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | Double-click the empty line (that only has a checkbox) at the bottom of the list, and a dialog will appear where you can add a new entry: | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | In this dialog, enter the following values: | ||
+ | |||
+ | * Enabled: //yes// | ||
+ | * Type: //host// | ||
+ | * Database: //all// (or only // | ||
+ | * User: //all// (or only // | ||
+ | * IP address: The address range of your LAN, see comments below | ||
+ | * Method: //md5// | ||
+ | |||
+ | The IP address range must be given in < | ||
+ | |||
+ | Don't forget to click OK and save the new '' | ||
+ | |||
+ | == Restart PostgreSQL service == | ||
+ | |||
+ | If you made any changes to the '' | ||
+ | |||
+ | Changes in the '' | ||
+ | |||
+ | == Adjust Windows Firewall == | ||
+ | |||
+ | The final step is to open TCP port 5432 in the firewall so that clients on the LAN can connect to our server. | ||
+ | |||
+ | If you use Windows Firewall (enabled by default on all recent Windows installtions), | ||
+ | |||
+ | In that window, right-click the node //Inbound Rules// in the tree on the left, then click //New Rule//. Select //Port// for as the rule type, then, on the next page, //TCP// as the protocol, and //5432// as the local port. Click //Next//, then select //Allow connection//, | ||
+ | |||
+ | ==== Linux ==== | ||
+ | |||
+ | PostgreSQL is available as a package in all major Linux distributions. | ||
+ | |||
+ | For the experienced Linux administrator, | ||
+ | |||
+ | Install PostgreSQL | ||
apt-get install postgresql | apt-get install postgresql | ||
| | ||
- | The graphical frontend pgAdmin III is also available as a package | + | Create |
+ | |||
+ | sudo -u postgres createuser -P mairlist | ||
+ | |||
+ | Create a database '' | ||
+ | |||
+ | sudo -u postgres createdb -O mairlist mairlist5 | ||
+ | |||
+ | To check if networking is enabled, edit the file ''/ | ||
+ | |||
+ | listen_addresses = ' | ||
+ | |||
+ | To grant access to the computers on your LAN (e.g. 192.168.178.x), | ||
+ | |||
+ | host all | ||
+ | |||
+ | Then restart the PostgreSQL daemon: | ||
+ | |||
+ | service postgresql restart | ||
+ | |||
+ | Now everything should be ready. | ||
+ | |||
+ | ===== Client setup ===== | ||
+ | |||
+ | See [[client-setup|this article]]. | ||
+ | |||
+ | Now that the PostgreSQL server is up and running, we can set up the clients, in other words, configure the database connection in mAirList. The database application is part of the normal setup package, and the connection setup is made in the same configuration app that is also used to configure the playout functions. | ||
+ | |||
+ | <WRAP center round tip> | ||
+ | If you are getting an error about " | ||
+ | </ | ||
+ | |||
+ | ==== First client computer ==== | ||
+ | |||
+ | === Connection setup === | ||
+ | |||
+ | On all client/ | ||
+ | |||
+ | Install mAirList, then open //Windows Start Menu -> All Programs -> mAirList -> Configuration// | ||
+ | |||
+ | The mAirListDB connection properties dialog | ||
+ | |||
+ | On the //General// tab, enter the host name or IP of the PostgreSQL server, the database name (// | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | When you click //Test login//, an error message will appear, saying that the " | ||
+ | |||
+ | Before you close the dialog and repeat the steps on the other client computers, go to the //Setup// tab to perform the initial setup tasks. | ||
+ | |||
+ | === Initial table setup === | ||
+ | |||
+ | Before mAirList can use the SQL database, you have to create the tables (database schema) in the still-empty SQL database. **This must only be done once, from the first client computer that you set up! Do not repeat this on the other computers!** | ||
+ | |||
+ | Go to the //Setup// tab and click //Perform initial setup//: | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | mAirList will now create the SQL tables necessary to hold the mAirListDB library and scheduling data. This might take a few seconds and will eventually be confirmed with the messsage //Setup completed successfully//: | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | ==== Additional clients ==== | ||
+ | |||
+ | If you want to connect additional mAirList computers to the database, go to the mAirList configuration app on each computer, and add the connection as described above. Now that we have created the tables, the //Test login// button should give the message: //Login OK// | ||
+ | |||
+ | When you click OK to save the connection, you will see the following message: | ||
+ | |||
+ | {{ : | ||
+ | Each mAirListDB has a unique identifier (made up of several random hexadecimal characters), |
tutorials/mairlistdb/setup-postgresql.txt · Last modified: 2019/07/09 16:18 by 127.0.0.1