Re: ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it

2021-01-23 Thread rob...@redo2oo.ch

Thanks Adrian

On 23.01.21 21:45, Adrian Klaver wrote:

On 1/23/21 12:36 PM, robert rottermann wrote:

Hi there

I made somehow a mess with my PostgreSQL installation an an ubuntu 18.4 with 
PostgreSQL 10.0


service postgresql status
● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor 
preset: enabled)

    Active: active (exited) since Sat 2021-01-23 21:21:13 CET; 2min 26s ago
   Process: 1853 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
  Main PID: 1853 (code=exited, status=0/SUCCESS)


What if you try:

sudo service postgresql@10-main status

Also  what does:

pg_lsclusters

show?

root@elfero-test:~/scripts# pg_lsclusters
Ver Cluster Port Status Owner    Data directory  Log file
10  main    5433 online postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log

root@elfero-test:~/scripts# service postgresql@10-main status
● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; indirect; vendor 
preset: enabled)

   Active: active (running) since Sat 2021-01-23 22:11:40 CET; 9h ago
  Process: 1348 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 
10-main start (code=exited, status=0/SUCCESS)

 Main PID: 1460 (postgres)
    Tasks: 7 (limit: 4543)
   CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service
   ├─1460 /usr/lib/postgresql/10/bin/postgres -D 
/var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf

   ├─1471 postgres: 10/main: checkpointer process
   ├─1472 postgres: 10/main: writer process
   ├─1473 postgres: 10/main: wal writer process
   ├─1474 postgres: 10/main: autovacuum launcher process
   ├─1475 postgres: 10/main: stats collector process
   └─1476 postgres: 10/main: bgworker: logical replication launcher

Jan 23 22:11:38 elfero-test systemd[1]: Starting PostgreSQL Cluster 10-main...

Jan 23 22:11:40 elfero-test systemd[1]: Started PostgreSQL Cluster 10-main.


Now I can also run psql. (why I could not before, I do not know ..)
BUT still I can not access the db from python.
even a simple python script like:

importpsycopg2
conn = psycopg2.connect(host="localhost", user="postgres", database="postgres")

Produces:

root@elfero-test:~/scripts# python test_elfero.py
Traceback (most recent call last):
  File "test_elfero.py", line 2, in 
    conn = psycopg2.connect(host="localhost", user="postgres", 
database="postgres")
  File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 130, in 
connect

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?


Any help is much appreciated

Robert



dynamically generate path to output file

2022-03-26 Thread rob...@redo2oo.ch

Hi Friends

I would like to generate the path of a CSV file to which I output a query.

This is what I try:

CREATEORREPLACEPROCEDUREexport_cvs(
home_dir varchar
)
AS
$BODY$
DECLARE
OUTFILE varchar;
BEGIN
OUTFILE = (home_dir || '/tmp/company.csv');
copy(
select'company_'||id as"External ID",
nameas"Name",'True'as"Is a Company",
email,
phone ,
company_registry
fromres_company
) TOOUTFILE withCSV HEADER;
END;
$BODY$
LANGUAGEplpgsql;

this produces a syntax error:

psql:export_contacts_short.sql:21: ERROR:  syntax error at or near "OUTFILE"
LINE 17: ) TO OUTFILE  with CSV HEADER;


It works fine when I replace "TO OUTFILE" with a hard coded string.

Can anybody of you give me a hint how to do that?

Thanks a lot.

Robert