2,306
edits
Changes
Jump to navigation
Jump to search
mLine 1:
Line 1: − + − + − + Line 10:
Line 10: − + − + − + − + − + Line 20:
Line 20: − + − + − + − 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 56:
Line 56: − + − + + + + + + + + + + + + − + − + − + + + Line 78:
Line 91: − + + − + + Line 94:
Line 109: − + − + − + − + − + − + − +
→Import .sql dump file
== Install ==
==Install==
=== Debian/Ubuntu ===
===Debian/Ubuntu===
sudo apt update
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo apt install postgresql postgresql-contrib
== 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 />
To allow remote access edit: /etc/postgresql/9.4/main/pg_hba.conf
To allow remote access edit: /etc/postgresql/9.4/main/pg_hba.conf
Example:
Example:
<nowiki> TYPE DATABASE USER CIDR-ADDRESS METHOD
<nowiki> TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 md5
# IPv6 local connections:
# IPv6 local connections:
host all all ::1/128 md5</nowiki>
host all all ::1/128 md5</nowiki>
2.- Change the Listen Address in postgresql.conf
2.- Change the Listen Address in postgresql.conf
set: listen_addresses = '*'
set: listen_addresses = '*'
== Crate users ==
==Crate users==
From postgres shell account type:
From postgres shell account type:
createuser --interactive
createuser --interactive
To change the user password:
To change the user password:
ALTER USER postgres WITH ENCRYPTED PASSWORD 'P@$$worD1';
ALTER USER postgres WITH ENCRYPTED PASSWORD 'P@$$worD1';
== Create database ==
==Create database==
From the postgres user shell:
From the postgres user shell:
createdb test1
createdb test1
== 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
== List databases ==
==List databases==
\list or \l
\list or \l
== List tables ==
==List tables==
\d
\d
\dt
\dt
== ADD, QUERY, DELETE from table ==
==ADD, QUERY, DELETE from table==
Add
Add
INSERT INTO table_name (column_name1, column_name2, column_name3, column_name4)
INSERT INTO table_name (column_name1, column_name2, column_name3, column_name4)
Update
Update
UPDATE table_name SET column_name1= 'value32' WHERE column_name2 = 'value2';
UPDATE table_name SET column_name1= 'value32' WHERE column_name2 = 'value2';
== ALTER TABLE ==
==ALTER TABLE==
ALTER TABLE table_name ADD column_name5 date;
ALTER TABLE table_name ADD column_name5 date;
ALTER TABLE table_name DROP column_name5;
ALTER TABLE table_name DROP column_name5;
== Import .sql dump file ==
== Dump database ==
<syntaxhighlight lang="bash">
g_dump -C -h localhost -U scrapy scrapydb > scrapydb_dump.sql
</syntaxhighlight>
=== Dump one database to another database ===
<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
</syntaxhighlight>
==Import .sql dump file==
psql databasename < data_base_dump
psql databasename < data_base_dump
pg_restore -h localhost -U postgres -d mapper -1 /tmp/mapper_before_refactoring_2018_11_13.sql
pg_restore -h localhost -U postgres -d mapper -1 /tmp/mapper_before_refactoring_2018_11_13.sql
== Error creating UTF-8 database ==
==Error creating UTF-8 database==
02:39:35: Error: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
02:39:35: Error: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
SUGERENCIA: Use the same encoding as in the template database, or use template0 as template.
SUGERENCIA: Use the same encoding as in the template database, or use template0 as template.
== Create user, database and password ==
==Create user, database and password==
* Create the user<br />
*Create the user<br />
postgres@hostname:~$ createuser
postgres@hostname:~$ createuser
CREATE ROLE
CREATE ROLE
* Create the DB
*Create the DB
postgres@hostname:~$ createdb databasename
postgres@hostname:~$ createdb databasename
CREATE DATABASE
CREATE DATABASE
postgres@hostname:~$
postgres@hostname:~$
* Grant access for the user to the DB
*Grant access for the user to the DB
postgres@hostname:~$ psql
postgres@hostname:~$ psql
postgres=# alter user username with encrypted password 'password';
postgres=# alter user username with encrypted password 'password';
Fix:
Fix:
<nowiki>update pg_database set datallowconn = TRUE where datname = 'template0';
<nowiki>update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
drop database template1;
create database template1 with template = template0 encoding = 'UTF8';
create database template1 with template = template0 encoding = 'UTF8';
update pg_database set datistemplate = TRUE where datname = 'template1';
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';</nowiki>
update pg_database set datallowconn = FALSE where datname = 'template0';</nowiki>