Tag: PostgreSQL – ServerOK

PostgreSQL connect to database using psql

Posted on March 1, 2021 by ServerOk

To connect to PostgreSQL database server using psql command line, run

su - postgres
psql

1 2	su - postgres psql

If you have a PostgreSQL user and password, you can use

psql -h SERVER_IP_HERE -p 5432 -U USER_NAME_HERE -W

1	psql -h SERVER_IP_HERE -p 5432 -U USER_NAME_HERE -W

Example

psql -h students.arrhwfpckmeo.ap-southeast-2.rds.amazonaws.com -p 5432 -U aite_students -W

1	psql -h students.arrhwfpckmeo.ap-southeast-2.rds.amazonaws.com -p 5432 -U aite_students -W

-W option is to force asking password. See PostgreSQL […]

postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files

Posted on January 31, 2021 by ServerOk

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:~$

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 to restart postgres with command

systemctl restart postgresql

1	systemctl restart postgresql

See PostgreSQL […]

Run PostgreSQL in Docker

Posted on June 30, 2019 (June 30, 2019) by ServerOk

To run PostgreSQL on docker, create a directory for saving the data presistant

mkdir -p /opt/my-postgresql

1	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

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 PostgreSQL server To connect to PostgreSQL server, run

docker container exec -ti my-postgresql bash

1	docker container exec -ti my-postgresql bash

Now you are inside PostgreSQL conainer, to login, run

psql -U serverok -W

1	psql -U serverok -W

It will ask for […]

PostgreSQL Export table into CSV file

Posted on June 5, 2019 by ServerOk

To export a table into CSV file, run

\copy TABLE_NAME to 'FILE_NAME.csv' csv header;

1	\copy TABLE_NAME to 'FILE_NAME.csv' csv header;

This will save the CSV file in current directory. […]

PostgreSQL List all databases

Posted on February 7, 2019 by ServerOk

To list all databases in PostgreSQL database, first connect to PostgreSQL database using psql command

su - postgres
psql

1 2	su - postgres psql

Now run

\list

\list

This will list all available databases. To change to a database, use command /connect

\connect DBNAME

1	\connect DBNAME

Once you connected to a database, you can list all tables in a database with command

\dt

\dt

[…]

PostgreSQL Create Database

Posted on November 17, 2018 (September 4, 2019) by ServerOk

To create a database, run

CREATE DATABASE "DB_NAME_HERE";

1	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 DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;
GRANT ALL PRIVILEGES ON DATABASE DB_NAME_HERE TO USER_NAME_HERE;

psql DB_NAME_HERE -c "GRANT ALL ON ALL TABLES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to USER_NAME_HERE;"
psql DB_NAME_HERE -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to USER_NAME_HERE;"

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 DB_NAME_HERE TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA DB_NAME_HERE TO USER_NAME_HERE;

GRANT ALL PRIVILEGES ON DATABASE DB_NAME_HERE TO USER_NAME_HERE;

psql DB_NAME_HERE -c "GRANT ALL ON ALL TABLES IN SCHEMA public to USER_NAME_HERE;"

psql DB_NAME_HERE -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to USER_NAME_HERE;"

psql DB_NAME_HERE -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to USER_NAME_HERE;"

Creating a User and Database using psql

su - postgres
createuser USERNAME_HERE
createdb --encoding=UTF8 --owner=USERNAME_HERE DB_NAME_HERE

su - postgres

createuser USERNAME_HERE

createdb --encoding=UTF8 --owner=USERNAME_HERE DB_NAME_HERE

[…]

PostgreSQL Delete a Database

Posted on November 17, 2018 by ServerOk

To delete a PostgreSQL database, run

DROP DATABASE DB_NAME;

1	DROP DATABASE DB_NAME;

Example

DROP DATABASE "odoodb2";

1	DROP DATABASE "odoodb2";

[…]

Backup PostgreSQL Database

Posted on June 6, 2018 by ServerOk

To backup PostgreSQL database, run

pg_dump  -h HOSTNMAME -p PORT -U USER -d DBNAME -W -f file.psql

1	pg_dump -h HOSTNMAME -p PORT -U USER -d DBNAME -W -f file.psql

Example

pg_dump  -h ukgaa1vy7o672pj.caqdhl3x5dow.us-east-1.rds.amazonaws.com -p 5432 -U serveroku -d serverokdb -W -f file.psql

1	pg_dump -h ukgaa1vy7o672pj.caqdhl3x5dow.us-east-1.rds.amazonaws.com -p 5432 -U serveroku -d serverokdb -W -f file.psql

PostgreSQL […]

Create user in PostgreSQL

Posted on April 8, 2018 (April 8, 2018) by ServerOk

Method 1 To create user, become user “postgres” with

su - postgres

1	su - postgres

now run

createuser USER_NAME

1	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 USAGE ON SCHEMA public TO USER_NAME_HERE;