How to Install PostgreSQL in FreeBSD
To Install PostgreSQL in FreeBSD
PostgreSQL is an open source relational database management system (DBMS). It can manage workloads of large internet facing applications with several concurrent users. It is mainly used for secure data storage. The postgreSQL supports transactions, subselects, triggers, views and sophisticated locking. Installation of PostgreSQL in FreeBSD is explained in this article.
Installation of PostgreSQL
Before installing the PostgreSQL, run the following command to update the mirrors.
root@linuxhelp:~ # freebsd-update fetch
Looking up update.FreeBSD.org mirrors... 4 mirrors found.
Fetching public key from update5.freebsd.org... done.
Fetching metadata signature for 10.3-RELEASE from update5.freebsd.org... done.
Fetching metadata index... done.
Fetching 2 metadata files... done.
Inspecting system... done.
Preparing to download files... done.
Fetching 379 patches.....10....20....30....40....50....60....70....80....90....100....110....120....130....140....150....160....170....180....190....200....210....220....230....240....250....260....270....280....290....300....310....320....330....340....350....360....370.... done.
Applying patches... done.
Fetching 9 files... done.
The following files will be added as part of updating to 10.3-RELEASE-p12:
/usr/src/contrib/libarchive/libarchive/test/test_write_disk_secure744.c
/usr/src/contrib/libarchive/libarchive/test/test_write_disk_secure745.c
/usr/src/contrib/libarchive/libarchive/test/test_write_disk_secure746.c
/usr/src/contrib/ntp/README.pullrequests
/usr/src/contrib/ntp/lib/isc/tsmemcmp.c
Then install the updated packages with the " update install" command.
root@linuxhelp:~ # freebsd-update install
Installing updates... done.
Now its time to install the PostgreSQL server and client.
root@linuxhelp:~ # pkg install postgresql93-server postgresql93-client Updating FreeBSD repository catalogue... Fetching meta.txz: 100% 944 B 0.9kB/s 00:01 Fetching packagesite.txz: 100% 5 MiB 213.6kB/s 00:27 Processing entries: 100% New packages to be INSTALLED: postgresql93-server: 9.3.14_1 postgresql93-client: 9.3.14_1 Number of packages to be installed: 2 The process will require 25 MiB more space. 5 MiB to be downloaded. Proceed with this action? [y/N]: y Fetching postgresql93-server-9.3.14_1.txz: 100% 3 MiB 150.7kB/s 00:22 Fetching postgresql93-client-9.3.14_1.txz: 100% 2 MiB 107.7kB/s 00:19 Checking integrity... done (0 conflicting) [1/2] Installing postgresql93-client-9.3.14_1... [1/2] Extracting postgresql93-client-9.3.14_1: 100% [2/2] Installing postgresql93-server-9.3.14_1... ===> Creating groups. Creating group ' pgsql' with gid ' 70' . ===> Creating users Creating user ' pgsql' with uid ' 70' . . . . . To set limits, environment stuff like locale and collation and other things, you can set up a class in /etc/login.conf before initializing the database. Add something similar to this to /etc/login.conf: --- postgres: :lang=en_US.UTF-8: :setenv=LC_COLLATE=C: :tc=default: --- and run `cap_mkdb /etc/login.conf' . Then add ' postgresql_class=" postgres" ' to /etc/rc.conf. ====================================================================== To initialize the database, run /usr/local/etc/rc.d/postgresql initdb You can then start PostgreSQL by running: /usr/local/etc/rc.d/postgresql start For postmaster settings, see ~pgsql/data/postgresql.conf NB. FreeBSD' s PostgreSQL port logs to syslog by default See ~pgsql/data/postgresql.conf for more info ====================================================================== To run PostgreSQL at startup, add ' postgresql_enable=" YES" ' to /etc/rc.conf
Next add the postgreSQL service to rc.conf file.
root@linuxhelp:~ # sysrc postgresql_enable=" YES"
postgresql_enable: -> YES
Then initialize the postgreSQL server with the following command.
root@linuxhelp:~ # service postgresql initdb
The files belonging to this database system will be owned by user " pgsql" .
This user must also own the server process.
The database cluster will be initialized with locale " C" .
The default text search configuration will be set to " english" .
Data page checksums are disabled.
creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling " trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/bin/postgres -D /usr/local/pgsql/data
or
/usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
Once the above process gets completed, configure the postgreSQL server as shown below,
root@linuxhelp:~ # nano -c /usr/local/pgsql/data/postgresql.conf
.
.
.
listen_addresses = ' *'
.
.
.
port = 5432
.
.
Done!
To Start PostgreSQL Service
Run the following command to start the postgresSQL service.
root@linuxhelp:~ # service postgresql start
LOG: ending log output to stderr
HINT: Future log output will go to log destination " syslog" .
Here you need to set user password for postgreSQL user.
root@linuxhelp:~ # passwd pgsql
Changing local password for pgsql
New Password:
Retype New Password:
Then utilize the " su" command to run the postgresql in command prompt.
Create the user and set password to create database.
root@linuxhelp:~ # su pgsql $ createuser -sdrP linuxhelpuser Enter password for new role: Enter it again: $ createdb -O linuxhelpuser linuxhelpdb $ psql linuxhelpdb psql (9.3.14) Type " help" for help. linuxhelpdb=# list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+---------------+----------+---------+-------+------------------- linuxhelpdb | linuxhelpuser | UTF8 | C | C | postgres | pgsql | UTF8 | C | C | template0 | pgsql | UTF8 | C | C | =c/pgsql + | | | | | pgsql=CTc/pgsql template1 | pgsql | UTF8 | C | C | =c/pgsql + | | | | | pgsql=CTc/pgsql (4 rows)
Q
How do I control connections from other hosts?
A
PostgreSQL only allows connections from the local machine using Unix domain sockets or TCP/IP connections. Other machines will not be able to connect unless you modify listen_addresses in the postgresql.conf file, enable host-based authentication by modifying the $PGDATA/pg_hba.conf file, and restart.
Q
What happened to the database dump feature in phpPgAdmin?
A
You need to configure phpPgAdmin (in the config.inc.php file) to point to the location of the pg_dump and pg_dumpall utilities on your server. Once you have done that, the database export feature will appear.
Q
Why phpPgAdmin ask me to re-enter login information for every page I access?
A
You most certainly have a problem with your PHP sessions. Make sure:
your browser accept cookies if your session ID relies on them
PHP has write access to the path set in the parameter “session.save_path” of your php.ini
your browser accept cookies if your session ID relies on them
PHP has write access to the path set in the parameter “session.save_path” of your php.ini
Q
How do I change the sort ordering of textual data in PostgreSQL?
A
PostgreSQL sorts textual data according to the ordering that is defined by the current locale, which is selected during initdb.
Q
Does the PostgreSQL be similar to MySQL?
A
No, IT is totally different from MYSQL.
Both systems are relational database systems and using SQL, the Structured Query Language, as a means for interacting with it. However, there are some differences between the MySQL and PostgreSQL syntax. ... PostgreSQL is case sensitive.
Both systems are relational database systems and using SQL, the Structured Query Language, as a means for interacting with it. However, there are some differences between the MySQL and PostgreSQL syntax. ... PostgreSQL is case sensitive.