PostgreSQL notes: Difference between revisions
Created page with "Linux - change to postgres user in command line interface # sudo su - postgres Backup database # pg_dump db_name > file_name Restore pg_dump file # psql db_name < f..." |
No edit summary |
||
Line 31: | Line 31: | ||
remote backup | remote backup | ||
pg_dump -U tecmint -h | pg_dump -U tecmint -h 192.168.1.9 -p 5432 cipher > 20220224_cipher.sql | ||
remote restore | |||
psql -U stevet -h 192.168.1.9 -p 5432 cipher < 20220224_cipher.sql | |||
Changing user privileges | Changing user privileges | ||
ALTER USER user_name WITH SUPERUSER/NOSUPERUSER; | ALTER USER user_name WITH SUPERUSER/NOSUPERUSER; |
Revision as of 12:41, 25 February 2022
Linux - change to postgres user in command line interface
# sudo su - postgres
Backup database
# pg_dump db_name > file_name
Restore pg_dump file
# psql db_name < file_name
Some table modification commands
ALTER TABLE some_table_name RENAME "row.names" TO id; ALTER TABLE some_table_name ALTER COLUMN id TYPE integer USING id::integer; ALTER TABLE some_table_name ADD PRIMARY KEY (id);
list databases
postgres # psql postgres=# \l
dump from one host to another
pg_dump -h host1 dbname | psql -h host2 dbname
Reload with
psql dbname < archive.sql
Create a database
createdb dbname
Using a zip archive with backup and restore
pg_dump dbname | gzip > filename.gz gunzip -c filename.gz | psql dbname
remote backup
pg_dump -U tecmint -h 192.168.1.9 -p 5432 cipher > 20220224_cipher.sql
remote restore
psql -U stevet -h 192.168.1.9 -p 5432 cipher < 20220224_cipher.sql
Changing user privileges
ALTER USER user_name WITH SUPERUSER/NOSUPERUSER;