{"id":468,"date":"2014-03-20T22:34:53","date_gmt":"2014-03-20T21:34:53","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=468"},"modified":"2014-03-20T22:34:53","modified_gmt":"2014-03-20T21:34:53","slug":"opensuse-13-2-postgresql-installation-by-zypper","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=468","title":{"rendered":"OpenSUSE 13.2, PostgreSQL installation (by zypper)"},"content":{"rendered":"

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<\/p>\n

<\/strong><\/p>\n

\u00a0ji2@linux:~> uname -a<\/strong><\/span><\/span>
\nLinux 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<\/span><\/p>\n

As you can see, it\u00b4s really „punk style“ \ud83d\ude42<\/p>\n

\u00a0PostgreSQL<\/strong><\/p>\n

9.3.3 version was released around 20th of February (2014). It\u00b4s 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\u00b4s try OpenSUSE.<\/p>\n

linux:~ # zypper info postgresql93-server<\/strong><\/span><\/span>
\nLoading repository data…<\/span>
\nReading installed packages…<\/span>
\nInformation for package postgresql93-server:<\/span>
\n——————————————–<\/span>
\nRepository: openSUSE-13.2-Oss<\/span>
\nName: postgresql93-server<\/span>
\nVersion: 9.3.3-2.1<\/span>
\nArch: x86_64<\/span>
\nVendor: openSUSE<\/span>
\nInstalled: No<\/span>
\nStatus: not installed<\/span>
\nInstalled Size: 15.7 MiB<\/span>
\nSummary: The Programs Needed to Create and Run a PostgreSQL Server<\/span>
\nDescription: <\/span>
\n PostgreSQL is an advanced object-relational database management system<\/span>
\n that supports an extended subset of the SQL standard, including<\/span>
\n transactions, foreign keys, sub-queries, triggers, and user-defined<\/span>
\n types and functions.<\/span>
\n This package includes the programs needed to create and run a<\/span>
\n PostgreSQL server, which will in turn allow you to create and maintain<\/span>
\n PostgreSQL databases.<\/span><\/p>\n

Seems promising! \ud83d\ude42<\/p>\n

linux:~ # zypper install postgresql93-server postgresql93 postgresql93-test<\/strong><\/span><\/span>
\nLoading repository data…<\/span>
\nReading installed packages…<\/span>
\nResolving package dependencies…<\/span><\/p>\n

The following 5 NEW packages are going to be installed:<\/span>
\n libpq5 postgresql-init postgresql93 postgresql93-server postgresql93-test<\/span><\/p>\n

5 new packages to install.<\/span>
\nOverall download size: 5.3 MiB. After the operation, additional 27.0 MiB will be used.<\/span>
\nContinue? [y\/n\/? shows all options] (y): <\/span>
\nRetrieving package postgresql-init-9.3-0.6.14.2.noarch (1\/5), 10.0 KiB ( 7.5 KiB unpacked)<\/span>
\nRetrieving: postgresql-init-9.3-0.6.14.2.noarch.rpm …………………………………………………..[done]<\/span>
\nRetrieving package libpq5-9.3.3-2.1.x86_64 (2\/5), 149.4 KiB (466.0 KiB unpacked)<\/span>
\nRetrieving: libpq5-9.3.3-2.1.x86_64.rpm ……………………………………………………………..[done]<\/span>
\nRetrieving package postgresql93-9.3.3-2.1.x86_64 (3\/5), 1.1 MiB ( 4.0 MiB unpacked)<\/span>
\nRetrieving: postgresql93-9.3.3-2.1.x86_64.rpm ………………………………………………………..[done]<\/span>
\nRetrieving package postgresql93-server-9.3.3-2.1.x86_64 (4\/5), 3.2 MiB ( 15.7 MiB unpacked)<\/span>
\nRetrieving: postgresql93-server-9.3.3-2.1.x86_64.rpm ……………………………………….[done (3.8 MiB\/s)]<\/span>
\nRetrieving package postgresql93-test-9.3.3-2.1.x86_64 (5\/5), 906.8 KiB ( 6.9 MiB unpacked)<\/span>
\nRetrieving: postgresql93-test-9.3.3-2.1.x86_64.rpm …………………………………………[done (3.3 MiB\/s)]<\/span>
\nChecking for file conflicts: ……………………………………………………………………….[done]<\/span>
\n(1\/5) Installing: postgresql-init-9.3-0.6.14.2 ……………………………………………………….[done]<\/span>
\nAdditional rpm output:<\/span>
\nUpdating \/etc\/sysconfig\/postgresql…<\/span>
\n(2\/5) Installing: libpq5-9.3.3-2.1 ………………………………………………………………….[done]<\/span>
\n(3\/5) Installing: postgresql93-9.3.3-2.1 …………………………………………………………….[done]<\/span>
\n(4\/5) Installing: postgresql93-server-9.3.3-2.1 ………………………………………………………[done]<\/span>
\n(5\/5) Installing: postgresql93-test-9.3.3-2.1 ………………………………………………………..[done]<\/span><\/p>\n

Installed in a 20 seconds.<\/p>\n

linux:~ # service postgresql start<\/strong><\/span><\/span>
\nlinux:~ #<\/span><\/p>\n

postgres@linux:~> psql<\/strong><\/span><\/span>
\npsql (9.3.3)<\/span>
\nType „help“ for help.<\/span><\/p>\n

Oh, really nice, also user postgres was created during lightning install. If we\u00b4re inside OpenSUSE, it is „a must“ to try anything within YaST2 (I think this tool is simply the best!). Let\u00b4s check services:<\/p>\n

\"\"<\/p>\n

You can switch to Enable this service if you wish to start this service automatically.<\/p>\n

linux:~ # cat \/etc\/services | grep postgres<\/strong><\/span><\/span>
\npostgresql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5432\/tcp\u00a0\u00a0\u00a0\u00a0 # PostgreSQL Database\u00a0 [Tom_Lane]<\/span>
\npostgresql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5432\/udp\u00a0\u00a0\u00a0\u00a0 # PostgreSQL Database\u00a0 [Tom_Lane]<\/span>
\nlinux:~ #<\/span><\/p>\n

If I want to use this SUSE machine as server, it is essential to set this server beyond localhost borders \ud83d\ude42<\/p>\n

postgres@linux:~> psql<\/strong><\/span><\/span>
\npsql (9.3.3)<\/span>
\nType „help“ for help.<\/span><\/p>\n

postgres=# show config_file;<\/strong><\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 config_file\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n-------------------------------------<\/span>
\n\u00a0\/var\/lib\/pgsql\/data\/postgresql.conf<\/span>
\n(1 row)<\/span><\/code><\/p>\n

postgres=#<\/span><\/p>\n

Ok, i know where is located main postgresql conf file (as well as other related .conf files).<\/p>\n

linux:~ # cat \/var\/lib\/pgsql\/data\/postgresql.conf | grep listen<\/strong><\/span><\/span>
\n#listen_addresses = ‚localhost‘\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# what IP address(es) to listen on;<\/span><\/p>\n

Well, this section should be looking similar:<\/p>\n

#——————————————————————————<\/span>
\n# CONNECTIONS AND AUTHENTICATION<\/span>
\n#——————————————————————————<\/span><\/p>\n

# – Connection Settings –<\/span><\/p>\n

listen_addresses = '*'<\/code><\/strong>\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# what IP address(es) to listen on;<\/span>
\n\u00a0 \u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# comma-separated list of addresses;<\/span>
\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# defaults to ‚localhost‘; use ‚*‘ for all<\/span>
\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# (change requires restart)<\/span>
\nport = 5432\u00a0\u00a0<\/strong> \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# (change requires restart)<\/span>
\nmax_connections = 100\u00a0<\/strong>\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0# (change requires restart)<\/span><\/p>\n

Adjust it according your needs. In same directory you can see essential network conf file for PostgreSQL – pg_hba.conf<\/strong>. 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:<\/p>\n

host\u00a0\u00a0 \u00a0all\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0all\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a00.0.0.0\/0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0trust<\/span><\/strong><\/p>\n

Restarting postgresql service is necessary:<\/p>\n

linux:\/var\/lib\/pgsql\/data # service postgresql stop<\/strong><\/span><\/span>
\nlinux:\/var\/lib\/pgsql\/data # service postgresql start<\/strong><\/span><\/span>
\nlinux:\/var\/lib\/pgsql\/data #<\/span><\/p>\n

You can have some client which is requiring database name for connection (AquaData Studio) – if you don\u00b4t remember default databases names, you can always check it:<\/p>\n

postgres=# select datname from pg_database;<\/strong><\/span><\/span>
\n\u00a0 datname \u00a0<\/span>
\n———–<\/span>
\n\u00a0template1<\/span>
\n\u00a0template0<\/span>
\n\u00a0postgres<\/span>
\n(3 rows)<\/span><\/p>\n

or a quick command with different output:<\/p>\n

postgres=# \\l<\/strong><\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 List of databases<\/span>
\n\u00a0\u00a0 Name\u00a0\u00a0\u00a0 |\u00a0 Owner\u00a0\u00a0 | Encoding |\u00a0\u00a0 Collate\u00a0\u00a0 |\u00a0\u00a0\u00a0 Ctype\u00a0\u00a0\u00a0 |\u00a0\u00a0 Access privileges\u00a0 \u00a0<\/span>
\n-----------+----------+----------+-------------+-------------+-----------------------<\/span>
\n\u00a0postgres\u00a0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | <\/span>
\n\u00a0template0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | postgres=CTc\/postgres<\/span>
\n\u00a0template1 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | postgres=CTc\/postgres<\/span>
\n(3 rows)<\/span><\/code><\/p>\n

On client side I\u00b4m checking connection status with SQLRunner<\/a>:<\/p>\n

\"\"<\/p>\n

All setup is fine:<\/p>\n

\"\"<\/p>\n

I\u00b4m really happy that zypper installation is 1) so quick 2) requires so few additional changes and setting up. Really nice work!!!<\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

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- … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,15],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/468"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=468"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/468\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}