2,306
edits
Changes
Jump to navigation
Jump to search
mLine 4:
Line 4: + + + Line 10:
Line 13: − + − # IPv4 local connections:+ − host all all 127.0.0.1/32 md5+ − # IPv6 local connections:+ − host all all ::1/128 md5</nowiki>+ Line 30:
Line 33: − CREATE TABLE table_name (+ − column_name1 col_type (field_length) column_constraints,+ − column_name2 col_type (field_length),+ − column_name3 col_type (field_length)+ − );+ − + − DROP TABLE table_name;</nowiki>+ Line 60:
Line 63: − + − + Line 109:
Line 112: − \c template0+ − update pg_database set datistemplate = FALSE where datname = 'template1';+ − drop database template1;+ − create database template1 with template = template0 encoding = 'UTF8';+ − update pg_database set datistemplate = TRUE where datname = 'template1';+ − \c template1+ − update pg_database set datallowconn = FALSE where datname = 'template0';</nowiki>+
→Install
sudo apt install postgresql postgresql-contrib
sudo apt install postgresql postgresql-contrib
== Data directory ==
<code>/var/lib/postgresql</code>
<br />
==Remote access==
==Remote access==
1.- Modify pg_hba.conf to add Client Authentication Record<br />
1.- Modify pg_hba.conf to add Client Authentication Record<br />
Example:
Example:
<nowiki> TYPE DATABASE USER CIDR-ADDRESS METHOD
<nowiki> TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5</nowiki>
2.- Change the Listen Address in postgresql.conf
2.- Change the Listen Address in postgresql.conf
==CREATE TABLE / DROP TABLE==
==CREATE TABLE / DROP TABLE==
<nowiki>
<nowiki>
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);
DROP TABLE table_name;</nowiki>
==List users==
==List users==
\du
\du
ALTER TABLE table_name DROP column_name5;
ALTER TABLE table_name DROP column_name5;
== Dump database ==
==Dump database==
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
g_dump -C -h localhost -U scrapy scrapydb > scrapydb_dump.sql
g_dump -C -h localhost -U scrapy scrapydb > scrapydb_dump.sql
</syntaxhighlight>
</syntaxhighlight>
=== Dump one database to another database ===
===Dump one database to another database===
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
g_dump -C -h localhost -U scrapy scrapydb | psql -h ec2-54-77-228-53.eu-west-1.compute.amazonaws.com -U scrapy scrapydb
g_dump -C -h localhost -U scrapy scrapydb | psql -h ec2-54-77-228-53.eu-west-1.compute.amazonaws.com -U scrapy scrapydb
Fix:
Fix:
<nowiki>update pg_database set datallowconn = TRUE where datname = 'template0';
<nowiki>update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UTF8';
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';</nowiki>