Postgresql: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
=== Postgresql === | === Postgresql === | ||
==== Authorize external connection ==== | ==== Authorize external connection ==== | ||
** Modify /etc/postgresql/12/main/postgresql.conf | ** Modify /etc/postgresql/12/main/postgresql.conf | ||
<pre>listen_addresses = ' | <pre>listen_addresses = '0.0.0.0/0'</pre> | ||
** Modify /etc/postgresql/12/main/pg_hba.conf | ** Modify /etc/postgresql/12/main/pg_hba.conf | ||
<pre>host all all 0.0.0.0/0 md5</pre> | <pre>host all all 0.0.0.0/0 md5</pre> | ||
** List active cluster | |||
<pre>pg_lsclusters</pre> | |||
==== Change postgres password ==== | ==== Change postgres password ==== | ||
| Line 13: | Line 15: | ||
then | then | ||
<pre>\password postgres</pre> | <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=== | ||
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