mAirList Wiki

User Tools

Site Tools

Trace:
tutorials:mairlistdb:setup-postgresql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
tutorials:mairlistdb:setup-postgresql [2014/10/01 15:35] – [Client Setup] twtutorials: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, we recommend to use PostgreSQL as the backend for any networked mAirListDB installation (where two or more mAirList computers or processes will share the same database). PostgreSQL is an open source database management system, available for Windows, Linux and other operating systems. Being free of charge, fast and networking-enabled, we recommend to use PostgreSQL as the backend for any networked mAirListDB installation (where two or more mAirList computers or processes will share the same database).
  
-====== Downloading and Installing PostgreSQL ======+===== PostgreSQL server setup =====
  
-===== Windows =====+==== Windows ====
  
 Binary packages/installers for Windows are available here: http://www.postgresql.org/download/windows/ (for the actual download, you will be taken to a site called EnterpriseDB) Binary packages/installers for Windows are available here: http://www.postgresql.org/download/windows/ (for the actual download, you will be taken to a site called EnterpriseDB)
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 ''postgresql-9.3.5-1-windows-x64.exe'') and run it. Download the appropriate setup file for your system (in this example ''postgresql-9.3.5-1-windows-x64.exe'') and run it.
Line 42: Line 42:
 </WRAP> </WRAP>
  
-==== Create user and database ====+=== Create user and database ===
  
 The next step is to create an SQL user and an empty database for mAirList. The next step is to create an SQL user and an empty database for mAirList.
Line 76: Line 76:
 Leave all other fields untouched. In particular, the encoding of the database (on the //Definition// tab) must be set to UTF-8. Click OK to create the new database. Leave all other fields untouched. In particular, the encoding of the database (on the //Definition// tab) must be set to UTF-8. Click OK to create the new database.
  
-==== Enable network access ====+=== 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 -- ''postgresql.conf'' and ''pg_hba.conf'' -- and then restart the PostgreSQL service. 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 -- ''postgresql.conf'' and ''pg_hba.conf'' -- and then restart the PostgreSQL service.
Line 84: Line 84:
 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. 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 ===+== Edit postgresql.conf ==
  
 In pgAdmin, go to //File -> Open postgresql.conf//, and locate the ''postgresql.conf'' file in the PostgreSQL data directory. The following dialog will appear: In pgAdmin, go to //File -> Open postgresql.conf//, and locate the ''postgresql.conf'' file in the PostgreSQL data directory. The following dialog will appear:
Line 96: Line 96:
 Don't forget to click Save if you have made any changes. Don't forget to click Save if you have made any changes.
  
-=== Edit pg_hba.conf ===+== Edit pg_hba.conf ==
  
 The second file, ''pg_hba.conf'', defines which databases should be accessible from which hosts and users, and which authentication method should be used. The second file, ''pg_hba.conf'', defines which databases should be accessible from which hosts and users, and which authentication method should be used.
Line 121: Line 121:
 Don't forget to click OK and save the new ''pg_hba.conf'' file. Don't forget to click OK and save the new ''pg_hba.conf'' file.
  
-=== Restart PostgreSQL service ===+== Restart PostgreSQL service ==
  
 If you made any changes to the ''postgresql.conf'' file, you must now restart the PostgreSQL service. You can either reboot the computer, or go to the Windows Control Panel, services, right-click the PostgreSQL service and restart it. If you made any changes to the ''postgresql.conf'' file, you must now restart the PostgreSQL service. You can either reboot the computer, or go to the Windows Control Panel, services, right-click the PostgreSQL service and restart it.
  
 Changes in the ''pg_hba.conf'' file do not necessarily require a restart, but you still have to reload the configuration. In the Windows Start Menu, PostgreSQL group, you will find a link //Reload configuration// which does the job. Changes in the ''pg_hba.conf'' file do not necessarily require a restart, but you still have to reload the configuration. In the Windows Start Menu, PostgreSQL group, you will find a link //Reload configuration// which does the job.
-==== Adjust Windows Firewall ====+ 
 +== 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. The final step is to open TCP port 5432 in the firewall so that clients on the LAN can connect to our server.
Line 133: Line 134:
  
 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//, click //Next//, select all network types, click //Next// again, choose a name (e.g. ''PostgreSQL server''), then click //Finish// to save. 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//, click //Next//, select all network types, click //Next// again, choose a name (e.g. ''PostgreSQL server''), then click //Finish// to save.
-===== Linux =====+ 
 +==== Linux ====
  
 PostgreSQL is available as a package in all major Linux distributions. The graphical frontend pgAdmin III is also available as a package (''pgadmin3''). If you want to use pgAdmin III, install those two packages, and then follow the instructions for creating a user and database, and enabling network access, from the Windows section above. PostgreSQL is available as a package in all major Linux distributions. The graphical frontend pgAdmin III is also available as a package (''pgadmin3''). If you want to use pgAdmin III, install those two packages, and then follow the instructions for creating a user and database, and enabling network access, from the Windows section above.
Line 165: Line 167:
 Now everything should be ready. Now everything should be ready.
  
-====== Client Setup ======+===== 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. 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.
  
-===== Seting up the first client computer =====+<WRAP center round tip> 
 +If you are getting an error about "unable to load libpq.dll" during client setup, you must install Microsoft Visual C++ 2010 Redistributable first: http://www.microsoft.com/en-us/download/details.aspx?id=8328 
 +</WRAP> 
 + 
 +==== First client computer ==== 
 + 
 +=== Connection setup ===
  
 On all client/mAirList computers that you want to access the mAirListDB, a database connection must be set up in the config. On all client/mAirList computers that you want to access the mAirListDB, a database connection must be set up in the config.
Line 185: Line 195:
 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. 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 =====+=== 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!** 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!**
Line 197: Line 207:
 {{ :tutorials:mairlistdb:pasted:20141001-173133.png }} {{ :tutorials:mairlistdb:pasted:20141001-173133.png }}
  
-===== Setting up additional clients =====+==== 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// 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//
Line 205: Line 215:
 {{ :tutorials:mairlistdb:pasted:20141001-173309.png }} {{ :tutorials:mairlistdb:pasted:20141001-173309.png }}
  
-Each mAirListDB has a unique identifier (made up of several random hexadecimal characters), the "Database ID". The ID is stored in the ''config'' table inside the SQL server, and also in the ''database.ini'' file of each client PC. It will help mAirList to identify the correct connection if you happen to maintain connections to more than one mAirListDB at a time.+Each mAirListDB has a unique identifier (made up of several random hexadecimal characters), the "Database ID". The ID is stored in the ''config'' table inside the SQL server, and also in the ''database.ini'' file of each client PC. It will help mAirList to identify the correct connection if you happen to maintain connections to more than one mAirListDB at a time. Clicking //Yes// in this dialog will retrieve the ID from the SQL table and store it in the local configuration. (You can look it up on the //Advanced// tab in the connection properties dialog if you like.)
tutorials/mairlistdb/setup-postgresql.1412177740.txt.gz · Last modified: 2019/07/09 16:19 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki