I love linux distributions alpha-releases. They are commonly more stable than any Windows family operating system + provides interesting new features sometimes. OpenSUSE 13.2 Milestone 0 was released 19.3.2014, providing btrfs as default file system, few minor changes and rolling- software repositories. I just wanted to try quick install and quick connection check with PostgreSQL
ji2@linux:~> uname -a
Linux linux.site 3.14.0-rc5-1-desktop #1 SMP PREEMPT Fri Mar 7 16:18:28 UTC 2014 (5fcd7b7) x86_64 x86_64 x86_64 GNU/Linux
As you can see, it´s really „punk style“ 🙂
PostgreSQL
9.3.3 version was released around 20th of February (2014). It´s very common that repositories stores more stable versions and only chance to install the latest one is to download + install it through binary package. Well, let´s try OpenSUSE.
linux:~ # zypper info postgresql93-server
Loading repository data…
Reading installed packages…
Information for package postgresql93-server:
——————————————–
Repository: openSUSE-13.2-Oss
Name: postgresql93-server
Version: 9.3.3-2.1
Arch: x86_64
Vendor: openSUSE
Installed: No
Status: not installed
Installed Size: 15.7 MiB
Summary: The Programs Needed to Create and Run a PostgreSQL Server
Description:
PostgreSQL is an advanced object-relational database management system
that supports an extended subset of the SQL standard, including
transactions, foreign keys, sub-queries, triggers, and user-defined
types and functions.
This package includes the programs needed to create and run a
PostgreSQL server, which will in turn allow you to create and maintain
PostgreSQL databases.
Seems promising! 🙂
linux:~ # zypper install postgresql93-server postgresql93 postgresql93-test
Loading repository data…
Reading installed packages…
Resolving package dependencies…
The following 5 NEW packages are going to be installed:
libpq5 postgresql-init postgresql93 postgresql93-server postgresql93-test
5 new packages to install.
Overall download size: 5.3 MiB. After the operation, additional 27.0 MiB will be used.
Continue? [y/n/? shows all options] (y):
Retrieving package postgresql-init-9.3-0.6.14.2.noarch (1/5), 10.0 KiB ( 7.5 KiB unpacked)
Retrieving: postgresql-init-9.3-0.6.14.2.noarch.rpm …………………………………………………..[done]
Retrieving package libpq5-9.3.3-2.1.x86_64 (2/5), 149.4 KiB (466.0 KiB unpacked)
Retrieving: libpq5-9.3.3-2.1.x86_64.rpm ……………………………………………………………..[done]
Retrieving package postgresql93-9.3.3-2.1.x86_64 (3/5), 1.1 MiB ( 4.0 MiB unpacked)
Retrieving: postgresql93-9.3.3-2.1.x86_64.rpm ………………………………………………………..[done]
Retrieving package postgresql93-server-9.3.3-2.1.x86_64 (4/5), 3.2 MiB ( 15.7 MiB unpacked)
Retrieving: postgresql93-server-9.3.3-2.1.x86_64.rpm ……………………………………….[done (3.8 MiB/s)]
Retrieving package postgresql93-test-9.3.3-2.1.x86_64 (5/5), 906.8 KiB ( 6.9 MiB unpacked)
Retrieving: postgresql93-test-9.3.3-2.1.x86_64.rpm …………………………………………[done (3.3 MiB/s)]
Checking for file conflicts: ……………………………………………………………………….[done]
(1/5) Installing: postgresql-init-9.3-0.6.14.2 ……………………………………………………….[done]
Additional rpm output:
Updating /etc/sysconfig/postgresql…
(2/5) Installing: libpq5-9.3.3-2.1 ………………………………………………………………….[done]
(3/5) Installing: postgresql93-9.3.3-2.1 …………………………………………………………….[done]
(4/5) Installing: postgresql93-server-9.3.3-2.1 ………………………………………………………[done]
(5/5) Installing: postgresql93-test-9.3.3-2.1 ………………………………………………………..[done]
Installed in a 20 seconds.
linux:~ # service postgresql start
linux:~ #
postgres@linux:~> psql
psql (9.3.3)
Type „help“ for help.
Oh, really nice, also user postgres was created during lightning install. If we´re inside OpenSUSE, it is „a must“ to try anything within YaST2 (I think this tool is simply the best!). Let´s check services:
You can switch to Enable this service if you wish to start this service automatically.
linux:~ # cat /etc/services | grep postgres
postgresql 5432/tcp # PostgreSQL Database [Tom_Lane]
postgresql 5432/udp # PostgreSQL Database [Tom_Lane]
linux:~ #
If I want to use this SUSE machine as server, it is essential to set this server beyond localhost borders 🙂
postgres@linux:~> psql
psql (9.3.3)
Type „help“ for help.
postgres=# show config_file;
config_file
-------------------------------------
/var/lib/pgsql/data/postgresql.conf
(1 row)
postgres=#
Ok, i know where is located main postgresql conf file (as well as other related .conf files).
linux:~ # cat /var/lib/pgsql/data/postgresql.conf | grep listen
#listen_addresses = ‚localhost‘ # what IP address(es) to listen on;
Well, this section should be looking similar:
#——————————————————————————
# CONNECTIONS AND AUTHENTICATION
#——————————————————————————
# – Connection Settings –
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to ‚localhost‘; use ‚*‘ for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
Adjust it according your needs. In same directory you can see essential network conf file for PostgreSQL – pg_hba.conf. Setup of this file requires some knowledeges – in my case im allowing that all you can imagine can connect into postgresql server by this line:
host all all 0.0.0.0/0 trust
Restarting postgresql service is necessary:
linux:/var/lib/pgsql/data # service postgresql stop
linux:/var/lib/pgsql/data # service postgresql start
linux:/var/lib/pgsql/data #
You can have some client which is requiring database name for connection (AquaData Studio) – if you don´t remember default databases names, you can always check it:
postgres=# select datname from pg_database;
datname
———–
template1
template0
postgres
(3 rows)
or a quick command with different output:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
On client side I´m checking connection status with SQLRunner:
All setup is fine:
I´m really happy that zypper installation is 1) so quick 2) requires so few additional changes and setting up. Really nice work!!!
-a-