Odenktools – Happy on research, love codings, opensource enthusiast.

Postgres Database Utility

April 5, 2022April 5, 2022 odenktools Leave a comment

Backup database (Linux)

pg_dump -U postgres -W --host localhost --port 5432 your_database | gzip > /opt/backup/your_database-$(date +%d-%m-%Y_%H-%M-%S).gz

1	pg_dump -U postgres -W --host localhost --port 5432 your_database \| gzip > /opt/backup/your_database-$(date +%d-%m-%Y_%H-%M-%S).gz

Backup database (Windows – Method 1)

pg_dump -U postgres -W --host localhost --port 5432 your_database > d:/your_database.sql

1	pg_dump -U postgres -W --host localhost --port 5432 your_database > d:/your_database.sql

Backup database (Windows – Method 2)

pg_dump -U postgres --host localhost --port 5432 --no-owner --no-privileges your_database > d:/your_database.sql

1	pg_dump -U postgres --host localhost --port 5432 --no-owner --no-privileges your_database > d:/your_database.sql

Restore database (Linux)

zcat /home/odenktools/your_database_2021-12-13T00:08:36Z.sql.gz | psql -U postgres -p 5432 -W your_database_name

1	zcat /home/odenktools/your_database_2021-12-13T00:08:36Z.sql.gz \| psql -U postgres -p 5432 -W your_database_name

Restore database (Linux / Windows)

psql -U postgres -W -d your_database -h localhost -p 5432 -f d:/your_database.sql

1	psql -U postgres -W -d your_database -h localhost -p 5432 -f d:/your_database.sql

Change default postgres password (Linux)

sudo -u postgres psql
\password postgres

1 2	sudo -u postgres psql \password postgres

Create New User (Linux – Method 1)

su - postgres
createuser your_user -s --pwprompt

1 2	su - postgres createuser your_user -s --pwprompt

Create New User (Linux – Method 2)

su postgres
psql -U postgres -d postgres -h localhost -p 5432
CREATE USER your_user WITH PASSWORD 'your_password';
ALTER ROLE your_user SUPERUSER;

su postgres

psql -U postgres -d postgres -h localhost -p 5432

CREATE USER your_user WITH PASSWORD 'your_password';

ALTER ROLE your_user SUPERUSER;

Create new Database (Linux – Method 1)

su - postgres
createdb your_new_database
psql -s your_new_database

su - postgres

createdb your_new_database

psql -s your_new_database

Create new Database (Linux – Method 2)

su postgres
psql -U postgres -h localhost -p 5432
CREATE DATABASE your_database WITH OWNER postgres ENCODING UTF8;

su postgres

psql -U postgres -h localhost -p 5432

CREATE DATABASE your_database WITH OWNER postgres ENCODING UTF8;

Connect to database

su postgres
psql -U postgres -d your_database -h localhost -W

1 2	su postgres psql -U postgres -d your_database -h localhost -W

Refresh MaterializeView

su postgres
psql -U postgres -d your_database -h localhost -W
REFRESH MATERIALIZED VIEW public.v_location_address WITH DATA;

su postgres

psql -U postgres -d your_database -h localhost -W

REFRESH MATERIALIZED VIEW public.v_location_address WITH DATA;

Install Laravel on Ubuntu 18.x

June 8, 2021June 8, 2021 odenktools Leave a comment

Installing Apache2

sudo apt install -y apache2

sudo apt install -y unzip zip curl

sudo a2enmod rewrite headers expires ssl && sudo service apache2 restart

sudo apt install -y apache2

sudo apt install -y unzip zip curl

sudo a2enmod rewrite headers expires ssl && sudo service apache2 restart

Installing PHP 7.2.x

sudo apt-cache search php7.2-

sudo apt install -y php7.2

sudo apt install -y php7.2-common php7.2-cli \
php7.2-gd \
php7.2-curl php7.2-intl \
php7.2-mbstring php7.2-bcmath \
php7.2-imap php7.2-xml php7.2-zip

sudo apt-cache search php7.2-

sudo apt install -y php7.2

sudo apt install -y php7.2-common php7.2-cli \

php7.2-gd \

php7.2-curl php7.2-intl \

php7.2-mbstring php7.2-bcmath \

php7.2-imap php7.2-xml php7.2-zip

Installing Mod Apache

sudo apt install -y libapache2-mod-php7.2

1	sudo apt install -y libapache2-mod-php7.2

Installing Mod For Nginx

sudo apt-get install php7.2-fpm

1	sudo apt-get install php7.2-fpm

Installing Mod MySQL

sudo apt install -y php7.2-mysql

1	sudo apt install -y php7.2-mysql

Installing Mod PostgreSQL

sudo apt install -y php7.2-pgsql

1	sudo apt install -y php7.2-pgsql

Create Swap Memory (For Low Memory VPS)

sudo fallocate -l 2G /swapfile

sudo chmod 600 /swapfile

sudo mkswap /swapfile

sudo swapon /swapfile

sudo fallocate -l 2G /swapfile

sudo chmod 600 /swapfile

sudo mkswap /swapfile

sudo swapon /swapfile

Installing Composer 1.x

sudo curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/local/bin --filename=composer --version=1.10.19

1	sudo curl -sS https://getcomposer.org/installer \| php -- --install-dir=/usr/local/bin --filename=composer --version=1.10.19

Or Installing Composer 2.x

sudo curl -sS https://getcomposer.org/installer | sudo php -- --install-dir=/usr/local/bin --filename=composer

1	sudo curl -sS https://getcomposer.org/installer \| sudo php -- --install-dir=/usr/local/bin --filename=composer

Setup Laravel Project

cd /var/www/html

sudo chmod -R o+w /var/www/html

cd /var/www/html

sudo chmod -R o+w /var/www/html

Create project from Laravel repository (Laravel 7.x Framework)

sudo composer create-project --prefer-dist laravel/laravel myapp "7.*"

sudo chgrp -R www-data /var/www/html/myapp

cd var/www/html/myapp/

sudo chmod -R ug+rwx storage bootstrap/cache

sudo chgrp -R www-data storage bootstrap/cache

sudo composer create-project --prefer-dist laravel/laravel myapp "7.*"

sudo chgrp -R www-data /var/www/html/myapp

cd var/www/html/myapp/

sudo chmod -R ug+rwx storage bootstrap/cache

sudo chgrp -R www-data storage bootstrap/cache

Installing PostgreSQL 10.x

cd /tmp/

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/postgres.list'

sudo apt update

sudo apt-get install -y postgresql-10

sudo service postgresql restart

cd /tmp/

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/postgres.list'

sudo apt update

sudo apt-get install -y postgresql-10

sudo service postgresql restart

Set password for DB administrator (postgres)

su - postgres

psql

postgres=# \password postgres

exit

su - postgres

createdb YOUR_DATABASE_NAME

su - postgres

psql

postgres=# \password postgres

exit

su - postgres

createdb YOUR_DATABASE_NAME

Drop Database

su - postgres

dropdb YOUR_DATABASE_NAME

su - postgres

dropdb YOUR_DATABASE_NAME

Create another Role

su - postgres

createuser YOUR_USERNAME -s --pwprompt

su - postgres

createuser YOUR_USERNAME -s --pwprompt

Installing Percona Xtra Cluster MySQL 5.7.x

sudo apt-get remove apparmor

1	sudo apt-get remove apparmor

sudo apt-get install software-properties-common

1	sudo apt-get install software-properties-common

apt-key adv --keyserver keys.gnupg.net --recv-keys 8507EFA5

apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8507EFA5

apt-key adv --keyserver keys.gnupg.net --recv-keys 8507EFA5

apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8507EFA5

sudo nano /etc/apt/source.list

1	sudo nano /etc/apt/source.list

deb http://repo.percona.com/apt bionic main testing
deb-src http://repo.percona.com/apt bionic main testing

1 2	deb http://repo.percona.com/apt bionic main testing deb-src http://repo.percona.com/apt bionic main testing

sudo apt-get update

1	sudo apt-get update

sudo apt-get install -y percona-xtradb-cluster-server percona-xtradb-cluster-server-5.7

1	sudo apt-get install -y percona-xtradb-cluster-server percona-xtradb-cluster-server-5.7

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

sudo service postgresql restart

1	sudo service postgresql restart

cd /etc/mysql/percona-xtradb-cluster.conf.d/

sudo cp mysqld.cnf mysqld.cnf.bak

cd /etc/mysql/percona-xtradb-cluster.conf.d/

sudo cp mysqld.cnf mysqld.cnf.bak

Add this command to last line

sudo nano mysqld.cnf

1	sudo nano mysqld.cnf

bind-address=0.0.0.0
pxc_strict_mode=DISABLED

1 2	bind-address=0.0.0.0 pxc_strict_mode=DISABLED

sudo service mysql restart

1	sudo service mysql restart

Create root user on any address

sudo mysql -uroot

1	sudo mysql -uroot

CREATE USER 'root'@'%' IDENTIFIED BY '123456';GRANT ALL on *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;

1	CREATE USER 'root'@'%' IDENTIFIED BY '123456';GRANT ALL on . TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;

jQuery Datatables and Laravel 6.x Pagination

May 17, 2020May 17, 2020 odenktools Leave a comment

jQuery DataTables is a table component that is powerful for data management. These plugins provide searching, sorting, pagination, filter per column, and many more. But we have some problems combining jQuery Datatables and Laravel framework.

We separate applications (Rest API and User Interface). For Rest API we use Laravel Framework and for User Interface use Laravel too, but we only Blade component and Guzzle as HTTP Client Library.

We now Laravel has good packages for handle it, as far I know Yajra’s good and incredible packages (you can see Yajra’s package on Github). But using Yajra’s library was not possible at that time, because Yajra was using Eloquent and can’t use HTTP Client (like Guzzle, UniRest, etc). Then we do research to deal with this problem and got results as we would discuss in this post.

On these tutorials, we use an empty Laravel 6.x project with Bootstrap 3, jQuery Datatables plugins. Our goal in this tutorial is to Display User Lists in Datatables (Searching and Sorting also). Ok let’s start the codings. Read More

Installing NodeJS On Ubuntu

April 26, 2020April 26, 2020 odenktools Leave a comment

Benchmarking Using AB

March 16, 2020March 16, 2020 odenktools Leave a comment

cd /mnt/d/ab/

1	cd /mnt/d/ab/

ab -p odenktoo001.json -T 'application/json' -c 10 -n 200 http://test.co.id/token

1	ab -p odenktoo001.json -T 'application/json' -c 10 -n 200 http://test.co.id/token

odenktoo001.json content :

{{ "grant_type": "password", "client_id": "1001", "username": "odenktools@gmail.com", "password": "qwerty", "scope": "*"}

1	{{ "grant_type": "password", "client_id": "1001", "username": "odenktools@gmail.com", "password": "qwerty", "scope": "*"}