Installing PostgreSQL on Linux and Using SQLynx for Database Management

This article demonstrates how to install PostgreSQL on a Linux system and how to use the database management tool SQLynx to connect to a database and perform operations such as creating databases and tables.

1. Installing the PostgreSQL Database

Installing PostgreSQL on Linux typically involves one of three common methods, each with its own set of advantages and disadvantages:

Binary Pre-compiled Package Installation:

• Advantages:
  • Quick and simple: Directly download the binary file for your platform; no compilation required, enabling a swift installation process.
  • Official support: The binary installation packages from the official provider are usually stable and thoroughly tested.
• Disadvantages:
  • Limited flexibility: May not meet specific configuration needs since options are pre-set at compile time.
  • Dependency handling: System dependencies may need to be manually resolved in some cases.

Installation via YUM:

• Advantages:
  • Convenience and automation: YUM installation automatically handles dependencies, simplifying the process.
  • Easy to manage: Ideal for package management on RPM-based systems (e.g., CentOS, Fedora), facilitating easy updates and maintenance.
  • Highly integrated with the system: The installation process is seamlessly integrated into system services, using tools like systemctl for management.
• Disadvantages:
  • Version limitations: May not always provide the latest version of PostgreSQL.
  • Configurations are more fixed: Manual adjustments may be needed post-installation for special configurations.

Source Code Installation:

• Advantages:
  • Highly customizable: Allows pre-installation customization through configuration options, meeting specific needs.
  • Access to the latest features: The newest versions and improvements are available through source install.
• Disadvantages:
  • Complexity: Requires managing dependencies and a compilation environment, suitable for those with experience.
  • Time-consuming: Downloading and compiling source code may take longer than other methods.

The following details the steps for YUM installation:

Adding the PostgreSQL Repository:

• Begin by adding the official PostgreSQL repository. Open a terminal and execute the following command to import PostgreSQL 12’s repository:

   sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Installing PostgreSQL:

• Once the repository is added, install PostgreSQL 12 using yum:

   sudo yum install -y postgresql12 postgresql12-server

• This installs PostgreSQL 12 database server and its accessories.

Initializing the Database:

• After installation, initialize the database storage with:

   sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

Starting the PostgreSQL Service:

• To launch the PostgreSQL service and ensure its automatic startup on system reboot, execute:

   sudo systemctl start postgresql-12
   sudo systemctl enable postgresql-12

Changing the postgres User Password:

• By default, PostgreSQL creates a system and database user named 'postgres'. To set a password, switch to the postgres user:

   sudo passwd postgres

• Then, switch users and connect to PostgreSQL to change the postgres database user’s password:

   su - postgres
   psql -d postgres -c "alter user postgres with password 'YourPassword';"

• Replace 'YourPassword' with your chosen secure password.

Enabling Remote Connections:

• PostgreSQL, by default, only allows local connections. To enable remote connections, edit the postgresql.conf and pg_hba.conf configuration files, commonly located at /var/lib/pgsql/12/data/.
• Ensure postgresql.conf includes the following (uncommented and set to the correct address or use '*' for all addresses):

   listen_addresses = '*'

• Then, in pg_hba.conf, add the following to permit remote connections:

   host    all             all             0.0.0.0/0               md5

• Save your changes and restart the PostgreSQL service:

   sudo systemctl restart postgresql-12

Verifying the Installation:

• Finally, verify that the PostgreSQL server is operational by creating a test database:

   su - postgres
   createdb testdb
   psql -d testdb

• Successful connection to the 'testdb' database signifies that PostgreSQL is correctly installed and operational on your CentOS 7 system.

2. Installing SQLynx

Download the Installation Package:

• Visit the website to select and download the appropriate version, for example, sqlynx_enterprise_linux_3.3.0.zip.

Unzip the File:

• Unzip the package to the current folder with the command:

   unzip sqlynx_enterprise_linux_3.3.0.zip

Start SQLynx:

• After unzipping, a sqlynx folder will be created. Enter this directory with:

   cd sqlynx

• Execute the following command:

   ./maicong-sqlynx.sh

• To start the service, execute:

   sh maicong-sqlynx.sh start

• After startup, SQLynx’s web interface can be accessed at http://<server IP address>:18888, where 18888 is the default port following installation.

Change the Port Number:

• Go into the sqlynx directory and update the configuration file as shown in the example. Edit the config/maicong.yaml file to change the port number (default is server.port:18888):

   vi config/maicong.yaml

• Press i to enter insert mode, make your changes to the port number, press esc to exit insert mode, and type :wq to save and quit.

Modify the JVM Heap Size:

• Within the sqlynx directory, execute the command to edit the maicong-sqlynx.sh file and adjust the heap sizes according to your server’s capabilities:

   vi maicong-sqlynx.sh

3. Using SQLynx to Connect to PostgreSQL

Start SQLynx:

• Open a browser and enter http://<server IP address>:18888 to reach the SQLynx login page. The default username is 'maicong'. The initial login password is directly entered by the user and saved for future logins.

Add a Data Source:

• Navigate to "System Settings" > "Data Configuration" > "Add Data Source".
• In the pop-up window, select the PostgreSQL icon and click "Next".
• On the "Basic Settings" page, fill in the PostgreSQL database information (such as server IP, port number, username, and password) you installed on Linux. Click "Test Connection", and if successful, click "OK" to complete the setup.

Return to the main page and refresh it; the configured PostgreSQL database information should now be visible. The expandable navigation bar allows for right-clicking on the data source name, database name, or object name to access related functions like creating databases or objects, importing/exporting data, and generating test data.

This concludes the guide on installing PostgreSQL on a Linux system and connecting to and operating the data source using SQLynx, a graphical database tool.