Postgresql: Difference between revisions
Jump to navigation
Jump to search
(Created page with "=== PgAdmin4=== ==== Reset Web Password ==== * Clear /var/lib/pgamdin folder * Reconfigure the web interface <pre> dpkg-reconfigure pgamin4-apache </pre>") |
No edit summary |
||
| (6 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
=== Postgresql === | |||
==== Authorize external connection ==== | |||
** Modify /etc/postgresql/12/main/postgresql.conf | |||
<pre>listen_addresses = '0.0.0.0/0'</pre> | |||
** Modify /etc/postgresql/12/main/pg_hba.conf | |||
<pre>host all all 0.0.0.0/0 md5</pre> | |||
** List active cluster | |||
<pre>pg_lsclusters</pre> | |||
==== Change postgres password ==== | |||
<pre>sudo -u postgres psql</pre> | |||
then | |||
<pre>\password postgres</pre> | |||
=== Manage User === | |||
** Create user with login | |||
<pre>CREATE ROLE username WITH LOGIN PASSWORD 'mysecret';</pre> | |||
** Provide Superuser to a user | |||
<pre>ALTER ROLE username SUPERUSER </pre> | |||
** List users | |||
<pre>select rolname,rolsuper,rolcanlogin FROM pg_roles; </pre> | |||
** List active cluster | |||
<pre>pg_lsclusters</pre> | |||
===Upgrade=== | |||
** List active cluster | |||
<pre>pg_lsclusters</pre> | |||
** Stop postgresql service | |||
<pre>service postgresql stop </pre> | |||
** Save database | |||
<pre>sudo -u postgres pg_dumpall > backup.sql </pre> | |||
** Install new postgres version | |||
<pre> apt-get install -y postgresql postgresql-client</pre> | |||
** Install option package | |||
<pre>curl -L https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add - | |||
sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list" | |||
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add - | |||
apt-get install -y timescaledb-2-postgresql-16 timescaledb-2-postgresql-16 | |||
</pre> | |||
** Upgrade database | |||
<pre> | |||
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \ | |||
--old-datadir=/var/lib/postgresql/14/main \ | |||
--new-datadir=/var/lib/postgresql/16/main \ | |||
--old-bindir=/usr/lib/postgresql/14/bin \ | |||
--new-bindir=/usr/lib/postgresql/16/bin \ | |||
--old-options '-c config_file=/etc/postgresql/14/main/postgresql.conf' \ | |||
--new-options '-c config_file=/etc/postgresql/16/main/postgresql.conf' | |||
</pre> | |||
=== PgAdmin4=== | === PgAdmin4=== | ||
==== Url ==== | |||
[http://localhost/pgadmin4 http://localhost/pgadmin4] | |||
==== Reset Web Password ==== | ==== Reset Web Password ==== | ||
* Clear /var/lib/pgamdin folder | * Clear /var/lib/pgamdin folder | ||
* Reconfigure the web interface | * Reconfigure the web interface | ||
<pre> dpkg-reconfigure pgamin4- | <pre> dpkg-reconfigure pgamin4-apache2 </pre> | ||
Latest revision as of 22:00, 2 March 2025
Postgresql
Authorize external connection
- Modify /etc/postgresql/12/main/postgresql.conf
listen_addresses = '0.0.0.0/0'
- Modify /etc/postgresql/12/main/pg_hba.conf
host all all 0.0.0.0/0 md5
- List active cluster
pg_lsclusters
Change postgres password
sudo -u postgres psql
then
\password postgres
Manage User
- Create user with login
CREATE ROLE username WITH LOGIN PASSWORD 'mysecret';
- Provide Superuser to a user
ALTER ROLE username SUPERUSER
- List users
select rolname,rolsuper,rolcanlogin FROM pg_roles;
- List active cluster
pg_lsclusters
Upgrade
- List active cluster
pg_lsclusters
- Stop postgresql service
service postgresql stop
- Save database
sudo -u postgres pg_dumpall > backup.sql
- Install new postgres version
apt-get install -y postgresql postgresql-client
- Install option package
curl -L https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add - sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list" wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add - apt-get install -y timescaledb-2-postgresql-16 timescaledb-2-postgresql-16
- Upgrade database
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/14/main \
--new-datadir=/var/lib/postgresql/16/main \
--old-bindir=/usr/lib/postgresql/14/bin \
--new-bindir=/usr/lib/postgresql/16/bin \
--old-options '-c config_file=/etc/postgresql/14/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/16/main/postgresql.conf'
PgAdmin4
Url
Reset Web Password
- Clear /var/lib/pgamdin folder
- Reconfigure the web interface
dpkg-reconfigure pgamin4-apache2