postgresql - Installation https://www.geeksforgeeks.org/how-to-install-postgresql-on-arch-based-linux-distributions-manjaro/ https://gist.github.com/NickMcSweeney/3444ce99209ee9bd9393ae6ab48599d8 - To use it `sudo su - postgres` or `psql -U postgres` Creating database when in user `create database ` delete database `drop database ` - Remote connection `psql -U postgres -p 5432 -h hostname` (5432 is default postgresql port number) or `psql -U postgres -p 5432 -h hostname -d database_name` -U is username -p for port -h for hostname -d for database name   To directly connect with url `psql postgres://user:password@ip_add_or_domain:port/db_name` or `PGPASSWORD="your_password" psql -U your_username -d your_database -h your_host` - Import and export Exporting postgresql Database We need to create a dump file. Here smart-brain is name of database `pg_dump -U postgres smart-brain > dump.sql` for remote `pg_dump -U postgres -p 5432 -h hostname -d database_name > dump.sql`   Importing postgresql Database `psql -U postgres smart-brain < dump.sql`   for archive compressed backups and restore backup ` pg_dump -U synapse_user -p 5432 -h localhost -d -Fc synapse > synapse_backup1.dump` restore `pg_restore -U synapse_user -p 5432 -h localhost -d synapse1 synapse_backup1.dump` - Store query output in a file `psql -d DatabaseName -U UserName -c "SELECT * FROM TABLE" -o /root/Desktop/file.txt` Saving query output in csv file https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file By using a meta command `\copy` `\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER` - Sometimes there is a error `current transaction is aborted, commands ignored until end of transaction block` then run this query `commit;` to fix this   insert only if the record with paricular value dont exist here unique_column is column with must have unique constraint. it must be a key ```sql INSERT INTO table_name (column1, column2) VALUES (val1, val2) ON CONFLICT (unique_column) DO NOTHING; ```       - set password of postgres user `ALTER USER username WITH PASSWORD 'newpassword';` # Setup Postgresql > run postgresql with systemctl ### Install postgres ##### latest `sudo pacman -S postgresql` ##### specific version > find version & build from source ### Check version `postgres --version` ### Confirm psql is not running `sudo systemctl status postgresql` --> should not be running ### Login as the postgres user > Note: always do this any time you are doing any type of admin work on psql `sudo su - postgres` ### Initialize data directory > Note: default db for psql is /var/lib/postgres/data `initdb --locale en_US.UTF-8 -D /var/lib/postgres/data` ### Logout of **postgres** user `exit` ### Confirm psql is still not running `sudo systemctl status postgresql` --> should not be running ### Start psql `sudo systemctl start postgresql` ### Confirm psql is running `sudo systemctl status postgresql` --> should be active ## Create user ### Log into postgres `sudo su - postgres` ### Create a new user > Note: user can be called anything however if you create a PostgreSQL user with the same name as your Linux username, it allows you to access the PostgreSQL database shell without having to specify a user to login (which makes it quite convenient). `createuser --interactive` or `sudo -u postgres bash` `createuser --pwprompt synapse_user` and for db `createdb --encoding=UTF8 --locale=C --template=template0 --owner=synapse_user synapse` - Enter name of role to add: `MY_LINUX_USERNAME` (add youur linux user else psql command wont work when using with that user ) - Shall the new role be a superuser?: `y` ### Logout of **postgres** user `exit` ### Restart psql `sudo systemctl restart postgresql` ### Confirm psql is running > Note: can see that it was restarted by looking at the timestamp on the ***Active*** field `sudo systemctl status postgresql`   ### Managing users To login as a newly created user in PostgreSQL, you typically need to use a client or interface that supports PostgreSQL, such as `psql`, PgAdmin, or another tool that allows database interaction. Here's how you can do it using `psql`, the command-line tool for PostgreSQL. 1. **Create the User**: First, you need to create the user (if you haven't done so already). You can do this by connecting to your PostgreSQL instance as a superuser (often `postgres`) and running a command like the following: First type `psql -U postgres` ```sql CREATE USER my_new_user WITH PASSWORD 'password'; ``` Adjust `my_new_user` and `'password'` according to your preferences. 2. **Grant Permissions** (if needed): Depending on what you want this user to do, you'll likely need to grant them some permissions or access to a database. Here's an example of how you can grant all privileges on a database named `my_database` to your new user: ```sql GRANT ALL PRIVILEGES ON DATABASE my_database TO my_new_user; ``` 3. **Login Using `psql`**: To login as the new user from the command line using `psql`, you would use a command like this: ```bash psql -d my_database -U my_new_user -W ``` - `-d my_database` specifies the database you are connecting to. - `-U my_new_user` specifies the user you are logging in as. - `-W` prompts `psql` to ask for the password of the user. (Note: For security reasons, it's recommended to use `-W` to prompt for the password instead of passing it directly in the command line.) 4. **Enter the Password**: After executing the above command, you'll be prompted to enter the password for `my_new_user`. Enter the password you have set for this account, and you should be logged in. 5. **Using Other Tools**: If you are using a GUI-based tool like PgAdmin, the process involves: - Open PgAdmin and connect to your PostgreSQL server. - Create a new server connection if your user has access to a different server or database.     - To delete users `drop user ` - Adding password for local connections By default there is `trust` authentication flag in `pg_hba.conf` file so we can login without requiring password. To change that just change trust to md5 Location of `pg_hba.conf` : `/var/lib/postgres/data/pg_hba.conf` Other authenticaor flags are - trust allows the connection without a password. - peer uses the operating system user identity to authenticate (common for local connections). - md5 or scram-sha-256 require a password - Changing password `alter user tpost with password 'qwerty';`   - Schema public permission error When creating table u may get error like `ERROR: permission denied for schema public` Then login to superuser ie `postgres` then run this `GRANT USAGE ON SCHEMA public TO tpost;` to grant permission Then restart the postgresql service   - running sql script in postgresql `psql -U username -d database_name` then `\i /path/to/your/script.sql;` Alternatively, you can execute the script directly from the command line without entering the MySQL shell `psql -U username -d database_name -f /path/to/your/script.sql`   in debian https://neon.tech/postgresql/postgresql-getting-started/install-postgresql-linux#connect-to-the-postgresql-database-server use this to login to psql `sudo -u postgres psql` or ```sh sudo -i -u postgres # then psql ``` or `psql -U postgres -p 5432 -h localhost` for non interactive password use `PGPASSWORD=PASS psql -U postgres -p 5432 -h localhost` or `psql postgresql://postgres:PASS@localhost:5432`   check database Many ways. Du -sh the folder. Dbdump the database, and there's a psql command for all the statistics too SELECT pg_size_pretty(pg_total_relation_size('""." or for specific database `SELECT pg_size_pretty(pg_database_size('your_database_name'));` shortcut `\l+ your_database_name` for all database ```sql SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC; ``` shortcut `\l+` https://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes