Postgresql
https://wiki.ubuntuusers.de/PostgreSQL/
Install
Install issues
If no cluster gets created automatically, kick it with:
pg_createcluster 9.4 main --start
Authentication
Connect as postgres (admin) user
sudo -i -u postgres
psql
Bitnami K8S image
psql -U postgres
You will be prompted to enter the postgres user password. This is the same as the application password.
Connect as different user
psql user_name -h 127.0.0.1 -d db_name
Change
Usage
Show DB:
\l
Use db:
\c openproject;
Show tables:
\dt
Create table:
CREATE TABLE accounts (
user_id SERIAL PRIMARY KEY,
username VARCHAR (50) UNIQUE NOT NULL,
password VARCHAR (50) NOT NULL,
);
Drop table:
DROP table accounts;
Show table:
\d accounts
Query data:
SELECT * FROM accounts;
List postgres users:
\du
Show hashed passwords:
select * from pg_shadow;
Etc
Rewriting string in all tables using dump/restore
systemctl stop openproject-puma.service nginx.service
su - postgres
pg_dump --clean openproject > openproject.before_path_rewrite.sql
Change and review:
sed 's/dokuwiki-alt/it\/dokuwiki-media/g' \
openproject.after_path_rewrite.sql > openproject.after_path_rewrite.sql
git diff --word-diff=color --word-diff-regex=. \
openproject.before_path_rewrite.sql openproject.after_path_rewrite.sql
Restore:
psql --set ON_ERROR_STOP=on openproject < openproject.after_path_rewrite.sql
pgloader
Migrate to PostgreSQL in a single command!
Install pgloader
- Alpine:
apk add pgloader