joplin-notes/joplin notion extension/Web Development/sql/postgresql.md

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 <database_name> delete database drop database <database_name>
• 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

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

   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:

   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:

   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 <username>

• 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

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

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