Postgres Database Utility

5 April, 202219 May, 2022 rahyan 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

8 June, 2021 rahyan 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;

Installing NodeJS On Ubuntu

26 April, 202016 May, 2020 rahyan Leave a comment

Protected: Linux Production Backup

30 May, 201830 May, 2018 rahyan

How to install MySQL Percona XtraDB Cluster from repositories

26 August, 201726 August, 2017 rahyan Leave a comment

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

sudo apt-get update

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

sudo apt-get update

sudo apt-get install libaio1

1	sudo apt-get install libaio1

(more…)