Any advice on PostgreSQL setup, everything i try seems to give me Ident authentication failed

[jb2cool]

Can you give me the 5min idiots guide on how to get this working even if it's just using the bundled demo data, everything i seem to try gives me;
psycopg2.OperationalError FATAL: Ident authentication failed for user "<username>"

I've got a PG user (postgres) that works, and i can enter the PSQL command line and create databases etc. but i can't seem to get the snapperdb.py script running to do this for me.

My PG username and password are entered into the user config but still no dice (This was something i didn't understand properly the last time i opened an issue).

I've even tried changing my pg_hba.conf to either md5 or trust.

The exact error i get is;

[sisadmin@jb snapperdb]$ ./snapperdb.py make_snpdb -c ebg4_config.txt
Namespace(command='make_snpdb', config_file='ebg4_config.txt', fastqs=[], log_dir='/home/sisadmin/snapperdb')
### Cant connect to SnapperDB ebg_4_snps
The SNPdb ebg_4_snps does not exist - running sql to create database
Traceback (most recent call last):
  File "./snapperdb.py", line 299, in <module>
    main()
  File "./snapperdb.py", line 294, in main
    run_command(args)
  File "./snapperdb.py", line 97, in run_command
    make_snpdb(config_dict)
  File "/home/sisadmin/snapperdb/snapperdb/snpdb/__init__.py", line 83, in make_snpdb
    snpdb.make_snpdb()
  File "/home/sisadmin/snapperdb/snapperdb/snpdb/snpdb.py", line 162, in make_snpdb
    conn = psycopg2.connect(make_db_conn_string)
  File "/usr/lib64/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  Ident authentication failed for user "postgres"

[flashton2003]

Try using the username and password you use to logon to your computer.

[timdallman]

Hi.

Credentials in the config should be as below:
Do the ones you are using allow you to connect to postgres via PSQL?

pg_uname $MYPOSTGRESUSERNAME
pg_pword $MYPOSTGRESPASSWORD
pg_host localhost (local host if postgres is running on the same machine as you are running snapper)

Also the user needs to have the authority to create databases and extension in postgres

You could try executing the template sql

template_snapperdb_denovo_refs_sql

in /snapperdb/snapperdb/snpdb/ to check if you the required permissions

Tim

[jb2cool]

I've built my machine up from scratch to make sure I had no rubbish in there.

From a vanilla CentOS machine

sudo yum install git postgresql-server python-devel python-pip
git clone https://github.com/phe-bioinformatics/snapperdb.git
pip2 install -e snapperdb --user
sudo postgresql-setup initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo su - postgres
createuser -d -s -P <myusername>
createdb <myusername>
sudo systemctl restart postgresql

This sets me up with a working Postgres install where I can enter the PSQL command line and create databases etc. I don't have to so anything funny to enter the PSQL command line, I can just call PSQL.

But it still fails the same as before.

I tried troubleshooting this by switching to my postgres user and then logging into PSQL using my username and this failed with a similar error;

sudo su - postgres
psql -d <myusername> -U <myusername> -W
Password for user <myusername>:
psql: FATAL: Peer authentication failed for user "<myusername>"

So this caused me to look at the pg_hba.conf file and change the Peer and Ident authentication methods to MD5. After changing these to MD5 and restarting the postgres service again I think it's working.

Using just the bundled data I get this;

[sisadmin@jb ~]$ cd snapperdb/
[sisadmin@jb snapperdb]$ ./snapperdb.py make_snpdb -c ebg4_config.txt
Namespace(command='make_snpdb', config_file='ebg4_config.txt', fastqs=[], log_dir='/home/sisadmin/snapperdb')
ebg_4_snps already exists
/home/sisadmin/snapperdb/reference_genomes/AM933172.R1.fastq /home/sisadmin/snapperdb/reference_genomes/AM933172.R2.fastq /home/sisadmin/snapperdb/reference_genomes/AM933172.fa
sh: wgsim: command not found
gzip: /home/sisadmin/snapperdb/reference_genomes/AM933172.R1.fastq: No such file or directory
gzip: /home/sisadmin/snapperdb/reference_genomes/AM933172.R2.fastq: No such file or directory
Warning: FastBinaryTree not available, using Python version BinaryTree.
Warning: FastAVLTree not available, using Python version AVLTree.
Warning: FastRBTree not available, using Python version RBTree.
[2017-11-17 12:49:45,878] INFO: Version: 1.3
[2017-11-17 12:49:45,879] INFO: Initialising data matrix.
[2017-11-17 12:49:45,887] INFO: Mapping data file with bwa.
[2017-11-17 12:49:45,921] ERROR: Mapping reads has failed.
[2017-11-17 12:49:45,921] ERROR: [M::bwa_idx_load_from_disk] read 0 ALT contigs
[E::main_mem] fail to open file `/home/sisadmin/snapperdb/reference_genomes/AM933172.R1.fastq.gz'.
[2017-11-17 12:49:45,922] WARNING: Could not map reads to the reference.
[2017-11-17 12:49:45,922] WARNING: Could not map reads to the reference. Aborting.
/home/sisadmin/snapperdb/reference_genomes/snpdb/AM933172.filtered.vcf not found ... 
[sisadmin@jb snapperdb]$

Does this look ok?

[timdallman]

Hi Jason.
Progress!
You are now missing some of the other dependencies.
It appears you don't have samtools installed (no wgsim error above)
Tim

[jb2cool]

I installed SAMtools (my PHEnix install script fetched my SAMtools install script but never actually ran it, that was a mistake on my part) and am now getting even further, you can see it actually doing something productive now;

[sisadmin@jb snapperdb]$ ./snapperdb.py make_snpdb -c ebg4_config.txt
Namespace(command='make_snpdb', config_file='ebg4_config.txt', fastqs=[], log_dir='/home/sisadmin/snapperdb')
ebg_4_snps already exists
FASTQs found for  AM933172
Warning: FastBinaryTree not available, using Python version BinaryTree.
Warning: FastAVLTree not available, using Python version AVLTree.
Warning: FastRBTree not available, using Python version RBTree.
[2017-11-20 12:33:11,369] INFO: Version: 1.3
[2017-11-20 12:33:11,370] INFO: Initialising data matrix.
[2017-11-20 12:33:11,374] INFO: Mapping data file with bwa.
[2017-11-20 12:36:06,577] INFO: Creating digitised variants with gatk.
[2017-11-20 12:38:55,124] INFO: Annotating
[2017-11-20 12:43:13,745] INFO: Applying filters: ['mq_score:30', 'min_depth:10', 'ad_ratio:0.9']
Traceback (most recent call last):
  File "./snapperdb.py", line 299, in <module>
    main()
  File "./snapperdb.py", line 294, in main
    run_command(args)
  File "./snapperdb.py", line 102, in run_command
    vcf_to_db(args, config_dict, vcf)
  File "/home/sisadmin/snapperdb/snapperdb/snpdb/__init__.py", line 52, in vcf_to_db
    snpdb.snpdb_upload(vcf,args)
  File "/home/sisadmin/snapperdb/snapperdb/snpdb/snpdb.py", line 440, in snpdb_upload
    if not self.check_duplicate(vcf, 'strains_snps'):
  File "/home/sisadmin/snapperdb/snapperdb/snpdb/snpdb.py", line 189, in check_duplicate
    dict_cursor.execute("select distinct(name) FROM %s where name = \'%s\'" % (database, vcf.sample_name))
  File "/home/sisadmin/.local/lib/python2.7/site-packages/psycopg2/extras.py", line 144, in execute
    return super(DictCursor, self).execute(query, vars)
psycopg2.ProgrammingError: relation "strains_snps" does not exist
LINE 1: select distinct(name) FROM strains_snps where name = 'AM9331...
                                   ^

[sisadmin@jb snapperdb]$

[timdallman]

it looks like the database did not form correctly when you made it manually.
if you can delete it and try make_snpdb function it should work

[jb2cool]

I'm going to wipe my machine out and start over so that i know all the steps needed. Fingers crossed i should be back to this same point in about an hour.

[jb2cool]

Ok, I have rebuilt from scratch and fixed a few issues along the way.

I didn't have the postgresql-contrib package installed so that was giving me a sql error about hstore.control, I have included that in my install script now.

How does this look?

[sisadmin@jb snapperdb]$ ./snapperdb.py make_snpdb -c ebg4_config.txt
Namespace(command='make_snpdb', config_file='ebg4_config.txt', fastqs=[], log_dir='/home/sisadmin/snapperdb')
### Cant connect to SnapperDB ebg_4_snps
The SNPdb ebg_4_snps does not exist - running sql to create database
/home/sisadmin/snapperdb/reference_genomes/AM933172.R1.fastq /home/sisadmin/snapperdb/reference_genomes/AM933172.R2.fastq /home/sisadmin/snapperdb/reference_genomes/AM933172.fa
[wgsim] seed = 1511186096
[wgsim_core] calculating the total length of the reference sequence...
[wgsim_core] 1 sequences, total length: 4685848
Warning: FastBinaryTree not available, using Python version BinaryTree.
Warning: FastAVLTree not available, using Python version AVLTree.
Warning: FastRBTree not available, using Python version RBTree.
[2017-11-20 13:57:26,802] INFO: Version: 1.3
[2017-11-20 13:57:26,803] INFO: Initialising data matrix.
[2017-11-20 13:57:26,809] INFO: Mapping data file with bwa.
[2017-11-20 14:00:18,726] INFO: Creating digitised variants with gatk.
[2017-11-20 14:03:03,854] INFO: Annotating
[2017-11-20 14:07:19,442] INFO: Applying filters: ['mq_score:30', 'min_depth:10', 'ad_ratio:0.9']
Calulated depth is 128.05 - cuttoff is 30
### Completed 2017-11-20 14:12:29.214813
[sisadmin@jb snapperdb]$ 

[timdallman]

Looks like success!
Thanks for your perseverance

[jb2cool]

I think we can mark this closed