How to install/configure/backups Postgresql on debian

NOTE: This page is intented to be used to develop features to postgresql. Do not use this for production!

https://www.postgresql.org/docs/current/installation.html

https://mindchasers.com/dev/postgres

some tools to get install before

$ apt-get install zsh tree git curl wget emacs libreadline6-dev systemtap-sdt-dev zlib1g-dev libssl-dev libpam0g-dev python-dev build-essential llvm-3.9 llvm-3.9-dev clang-3.9 clang-3.9-dev bison flex libreadline-dev libreadline

to enable TAP tests:

$ perl -MCPAN -e shell
install CPAN
reload CPAN
install TAP::Parser::SourceHandler::pgTAP
install IPC::Run

Then:

$ cd postgresql
$ mkdir builds && cd $_
../configure --enable-debug --enable-depend --enable-tap-tests --enable-cassert CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer"
$ make
$ make check
# needs root access below (go to the postgresql directory)
$ su -
$ make install
$ /sbin/ldconfig /usr/local/pgsql/lib
$ adduser --system --home /usr/local/pgsql/data --shell /bin/bash --group postgres
$ passwd postgres
# logout from root access
$ su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --pwprompt
$ echo 'PATH=/usr/local/pgsql/bin:$PATH' >> ~/.zshrc
$ echo 'export PGDATA=/usr/local/pgsql/data' >> ~/.zshrc

Challenge: can you implement your own small database engine ?

A Simple and Efficient Implementation for Small Databases by Andrew D. Birreil Michael B. Jones Edward P. Wobber*

http://birrell.org/andrew/papers/024-DatabasesPaper-SOSP.pdf

Based on that paper, you can write a small database engine. I suggest you to add a CLI tool,  a SQL parser and executor .. for example!

You could take a look at the SQLite code source to get ideas how this is implemented. By doing that, you get a better understanding how databases work.

If you did, please comment 🙂

Postgresql-Get the number of rows affected

You will find a way below to get the number of rows affected by an UPDATE or a DELETE statement.

 
CREATE FUNCTION update_foobar(id INT, field1 VARCHAR(40))
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    -- some variables declarations
BEGIN
    -- UPDATE or DELETE
    -- then 
    GET DIAGNOSTICS ru := ROW_COUNT;
    RETURN ru;
END;
$$ ;

Setup sftp server on debian8 (chroot)

Let’s start by:

su -
groupadd sftp_users
usermod -G sftp_users my_username

Then:

emacs /etc/ssh/sshd_config
 
# Search the line below and replace it
Subsystem sftp /usr/lib/openssh/sftp-server
# by this one
Subsystem sftp internal-sftp
 
# Now each time that a user connects from the sftp group, 
# we are gonna apply theses rules:
 
Match Group sftp_users
 X11Forwarding no
 AllowTcpForwarding no
 ChrootDirectory /home
 ForceCommand internal-sftp

Finally:

systemctl restart ssh

https://serverfault.com/questions/660160/openssh-difference-between-internal-sftp-and-sftp-server

Edit: If you need to remove a user from a group:

gpasswd -d user group

or debian includes a tool:

deluser user group

https://unix.stackexchange.com/questions/29570/how-do-i-remove-a-user-from-a-group

Publier pour ne pas oublier