PostgreSQL connect to database using psql
To connect to the PostgreSQL database server using psql command line, run If you have a PostgreSQL user and password, you can use Example -W option is to force asking password. See PostgreSQL
PostgreSQL
postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
When i restart postgresql, i get following error postgres@vmi465483:~$ systemctl restart postgresql.service Failed to restart postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files See system logs and ‘systemctl status postgresql.service’ for details. postgres@vmi465483:~$ This is because you are running the command as user postres. Login as user “root”, you will be able … Read more
Run PostgreSQL in Docker
To run PostgreSQL on docker, create a directory for saving the data presistant mkdir -p /opt/my-postgresql run docker container docker run –name my-postgresql \ -p 5432:5432 \ -e POSTGRES_PASSWORD=serverok123 \ -e POSTGRES_USER=serverok \ -e POSTGRES_DB=serverok \ -v /opt/my-postgresql:/var/lib/postgresql/data \ -d postgres In above, change the value for POSTGRES_DB, POSTGRES_USER and POSTGRES_PASSWORD as needed. Connect to … Read more
PostgreSQL Export table into CSV file
To export a table into CSV file, run \copy TABLE_NAME to ‘FILE_NAME.csv’ csv header; This will save the CSV file in current directory.
PostgreSQL List all databases
To list all databases in the PostgreSQL database, first connect to the PostgreSQL database using psql command Now run This will list all available databases. To change to a database, use the command /connect Once you are connected to a database, you can list all tables in a database with the command
PostgreSQL Create Database
To create a database, run CREATE DATABASE “DB_NAME_HERE”; Creating user, grant permissions CREATE USER USER_NAME_HERE WITH PASSWORD ‘BAPT788HZutBdV’; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE; GRANT USAGE ON SCHEMA public TO USER_NAME_HERE; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA … Read more
PostgreSQL Delete a Database
To delete a PostgreSQL database, run DROP DATABASE DB_NAME; Example DROP DATABASE “odoodb2”;
Backup PostgreSQL Database
To backup PostgreSQL database, run Example If you don’t have PostgreSQL installed, you can use docker, pull the appropriate version of docker image, in this case i used version 13.5, same version used by Amazon RDS, then run It will prompt for the password, once you enter the password, the database will be backed up … Read more
Create user in PostgreSQL
Method 1 To create user, become user “postgres” with su – postgres now run createuser USER_NAME Method 2 You can run following commands in psql prompt CREATE USER USER_NAME_HERE WITH PASSWORD ‘PASSWORD_HERE’; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER_NAME_HERE; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO USER_NAME_HERE; GRANT … Read more
pg_ctl
pg_ctl is used to start and stop the PostgreSQL server. To start PostgreSQL, run You can also use stop/restart. Back to PostgreSQL
PostgreSQL
psql commands \l List databases \l+ List databases \c DB_NAME connect to a database \du list users \dt list tables \d TABLE_NAME describe a table \dn+ List of schemas To install PostgreSQL on Ubuntu/Debian, run Start it with By default user “postgres” have full access on PostgreSQL. To create a user, run To create a … Read more