blob: e160ae29c3cd3d583a548763f30706e982b78e80 (
plain) (
tree)
|
|
<!DOCTYPE html>
<html dir="ltr" lang="en">
<head>
<meta charset='utf-8'>
<title>Postgresql</title>
</head>
<body>
<a href="index.html">Tools Index</a>
<h1>Postgresql</h1>
<h2 id="install">1.1. Install Postgresql</h2>
<pre>
$ prt-get depinst postgresql
</pre>
<p>Mount Point;</p>
<pre>
# mkdir -p /srv/pgsql
# mount /srv/pgsql
</pre>
<p>Create user;</p>
<pre>
# useradd -U -m -d /srv/pgsql -s /bin/false postgres
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
</pre>
<pre>
# passwd -l postgres
passwd: password expiry information changed.
# touch /var/log/pgsql
# chown -R postgres:postgres /srv/pgsql /var/log/pgsql
# ldconfig /user/lib/postgresql
</pre>
$ sudo -u postgres mkdir -p /srv/pgsql/data
# sudo -u postgres touch /srv/pgsql/.psql_history
</pre>
<p>Create /etc/logrotate.d/postgres;</p>
<pre>
/var/log/pgsql {
weekly
compress
delaycompress
rotate 10
notifempty
create 660 postgres postgres
}
</pre>
<p>Edit /etc/rc.d/postgresql;</p>
<pre>
#
# /etc/rc.d/postgresql: start, stop or restart PostgreSQL server postmaster
#
PG_DATA=/srv/pgsql/data
PG_HOME=/srv/pgsql
case "$1" in
start|stop|status|restart|reload)
(cd $PG_HOME && sudo -u postgres pg_ctl -D "$PG_DATA" -l /var/log/pgsql "$1")
;;
*)
echo "usage: $0 start|stop|restart|reload|status"
;;
esac
# End of file
</pre>
<h2 id="config">1.2. Configure Server</h2>
<pre>
# sudo -u postgres initdb -D /srv/pgsql/data
</pre>
<p>Create password for super user;</p>
<pre>
# su postgres
$ psql -U postgres
</pre>
<p>Edit /pgsql/data/<a href="../conf/srv/pgsql/data/postgresql.conf">postgresql.conf</a>;</p>
<pre>
log_destination = 'syslog' # Can specify multiple destinations
syslog_facility='LOCAL0'
syslog_ident='postgres'
log_connections = on
password_encryption=on
</pre>
<p>Edit /srv/pgsql/data/<a href="../conf/srv/pgsql/data/pg_hba.conf">pg_hba.conf</a>;</p>
<pre>
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all trust
local all postgres ident
# IPv4 local connections:
hostssl all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres trust
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust
</pre>
<p><a href="syslog-ng.html">Configure Syslog-ng</a>, check <a href="http://michael.otacoo.com/postgresql-2/postgres-settings-simple-syslog-configuration-with-syslog-ng/">Michael at otacoo</a> article. Example;</p>
<pre>
destination postgres { file("/var/log/pgsql"); };
filter f_postgres { facility(local0); };
log { source(s_log); filter(f_postgres); destination(postgres); };
</pre>
<p>Start server and alter postgres password</p>
<pre>
# /etc/rc.d/postgresql start
</pre>
<pre>
postgres=# alter user postgres with password 'new_password';
</pre>
<h2 id="createuser">1.3. Create User</h2>
<p>Create a new user with createuser command;</p>
<pre>
$ sudo -u postgres createuser --pwprompt --encrypted \
--no-createrole --no-createdb laravel_user
Enter password for new user:
Enter it again:
</pre>
<h2 id="createdb">1.4. Create Database</h2>
<p>Create a new database for new user with createdb command;</p>
<pre>
$ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
--owner=laravel_user laravel_db
</pre>
<h2 id="dropdb">1.5. Drop Database</h2>
<p>Deleting database with dropdb command;</p>
<pre>
sudo -u postgres dropdb laravel_db
</pre>
<h2 id="dropuser">1.6. Drop User</h2>
<p>Deleting user with dropuser command;</p>
<pre>
sudo -u postgres dropuser laravel_user
</pre>
<h2 id="psql">1.7. Psql</h2>
<p>Lets check with psql, login with user postgres;</p>
<pre>
$ sudo -u postgres psql
</pre>
<p>First show help;</p>
<pre>
postgres=# \?
</pre>
<h3 id="listdb">Psql - List Databases and Roles</h3>
<p>List roles then list databases;</p>
<pre>
postgres=# \dg
postgres=# \l
</pre>
<h3 id="psqldb">Psql - Create Database</h3>
<pre>
postgres=# create database laravel_db_ext owner laravel_user encoding 'UTF-8' template template0;
</pre>
<h3 id="droptables">Psql - Drop Tables</h3>
<p>This example assumes that all tables,
are in public schema. First connect to database and
list tables;</p>
<pre>
postgres=# \c laravel_db
postgres=# \dt
</pre>
<p>Drop all tables on public schema and recreate public schema;</p>
<pre>
laravel_db=# drop schema public cascade;
laravel_db=# create schema public;
</pre>
<h3 id="backup">Backup</h3>
<p>Backup Database</p>
<h3 id="backup">Restore</h3>
<pre>
$ psql laravel_db < database_dump
</pre>
<a href="index.html">Tools Index</a>
<p>
This is part of the c9-doc Manual.
Copyright (C) 2016
c9 team.
See the file <a href="../fdl-1.3-standalone.html">Gnu Free Documentation License</a>
for copying conditions.</p>
</body>
</html>
|